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.

You could build your query with a text editor that supports regex find/replace which will allow you to copy the name of the column as the alias to the ISNULL() for each column. Combine this with a query to sys.columns to get a list of the columns on that table.... — rory.ap
– rory.ap, Commented Feb 27, 2017 at 14:54
You can create a view of this table which returns all columns. Next time select from that view. This way you don't have to write always these conditions. — Nikhil Agrawal
– Nikhil Agrawal, Commented Feb 27, 2017 at 14:55
I did used regex with notepad++ from creation query script. Thanks — Mr. Developer
– Mr. Developer, Commented Feb 27, 2017 at 15:23

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related