havin havin - 1 month ago 15
SQL Question

isnull for dynamically Generated column

I am getting temp table with dynamically generated columns let say it is columns

A,B,C,D
etc from other source.

Now in my hand I have temp table with column generated. I had to write stored procedure with the use of temp table.

So my stored procedure is like

create proc someproc()
as
begin
Insert into #searchtable
select isnull(#temp.*,0.00)
End


Now
#searchresult
is table created by me to store temp table columns. The problem arises when I want to check
isnull
for
#tempdb
columns. Because from source it comes it may be 3 columns, again next time it may be 4 columns. It changes.

Since it is dynamically generated I cannot use each column name and use like below:

isnull(column1,0.00)
isnull(column2,0.00)


I had to use all column generated and check if value is empty use
0.00


I tried this below but not working:

isnull(##temp.*,0.00),

Answer

Try with Dynamic code by fetching the column name for your dynamic table from [database].NFORMATION_SCHEMA.COLUMNS

--Get the Column Names for the your dynamic table and add the ISNULL Check:

DECLARE @COLS VARCHAR(MAX) = ''

SELECT @COLS = @COLS + ', ISNULL(' + COLUMN_NAME + ', 0.00) AS ' + COLUMN_NAME
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#temp[_]%' -- Dynamic Table (here, Temporary table)

DECLARE @COLNAMES VARCHAR(MAX) = STUFF(@COLS, 1, 1, '')

--Build your Insert Command:

DECLARE @cmd VARCHAR(MAX) = '
INSERT INTO #temp1
SELECT ' + @COLNAMES + ' FROM #temp'

--Execute:

EXEC (@cmd)