Set 0 instead of null with SQL query in SQL Server (C#)

Question

I have a table with 500 columns, and many of these are null, also I can not change the database.

It's a shared project.

My problem is null, I would need to put 0 instead of null, but they are obliged to do so in the query.

I tried with:

SELECT isnull(mycolumn, 0) 
FROM RGP_RegistrazioneParametri 
WHERE RGP_MCC_Numero_Serie_MS = xxxx

but without success .... in this instance I should put the name for the column, and since they are 500 columns, I ask ... there is a method to do this automatically on all null?

Thank you.

Veljko89 · Accepted Answer · 2017-02-27 15:02:53Z

1

You could make a dynamic sql for it

declare @cols nvarchar(max)
select @cols = STUFF((SELECT ',' + QUOTENAME(col.name) + ' = case when ' 
                      + QUOTENAME(col.name) + ' = NULL then 0 else ' 
                      + QUOTENAME(col.name) + ' end'
                      from sys.columns col
                        join sys.tables tab on col.object_id = tab.object_id
                        where tab.name = 'RGP_RegistrazioneParametri'
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')  

declare @sql nvarchar(max) = 'update RGP_RegistrazioneParametri 
                              set ' + @cols + '
                              WHERE RGP_MCC_Numero_Serie_MS = xxxx'
select @sql
-- execute (@sql)

edited Feb 27, 2017 at 15:02

answered Feb 27, 2017 at 14:57

Veljko89

1,9533 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:09:26Z

0

It is doable, but it involves dynamic SQL. Please read How Can we use ISNULL to all Column Names in SQL Server 2008?

The accepted answer is very similar to what you need.

Hope it helps.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Feb 27, 2017 at 14:57

Mihai Ovidiu Drăgoi

1,3171 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

Set 0 instead of null with SQL query in SQL Server (C#)

2 Answers 2

Comments

Comments

Your Answer

Collectives™ on Stack Overflow

Set 0 instead of null with SQL query in SQL Server (C#)

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest