Convert Stored procedure result from NULL to 0

Question

I have temp table accepting result set of executing stored proc. I can't change stored proc. The sp return null columns in some rows but I want to convert NULL to 0 when insert them into the temp table. How to do it easily?

My SQL is like:

Insert into #temp (co1, co2, co3)
exec sp_xxx

co3 from sp_xxx might be NULL, but I want to convert it to 0 and store in #temp.col3

There is no good way of doing this. For a non temporary table you could use an `INSTEAD OF` trigger but not possible on a `#temp` table. If you can't change the stored procedure then maybe easiest thing would be to add a computed column that does the desired conversion and reference that instead? Well [I suppose you could always use this technique](http://stackoverflow.com/a/1228165/73226) but seems completely unnecessary. — Martin Smith, Feb 27 '14 at 12:07

score 5 · Answer 1 · answered Feb 27 '14 at 11:04

5

Give it a try,

ISNULL(COLUMN_NAME, 0)

answered Feb 27 '14 at 11:04

BAdmin

927
1
11
19

I did like this: Insert into #temp (co1, co2, ISNULL(co3,0)) exec sp_xxx Incorrect syntax error got. – Sheen Feb 27 '14 at 11:36
1

You can do one thing, after inserting in `#temp` get the result from `#temp` as SELECT co1,co2,ISNULL(co3,0) AS co3 FROM #temp. Let me workout more on this, if i get anything as your requirement i'll get back to you. – BAdmin Feb 27 '14 at 11:52

Vikram Jain · Answer 2 · 2014-02-27T12:29:34.763

1

 Insert into #temp (co1, co2, co3);
    exec sp_xxx;

-- update value where column have null value :

  update #temp set col1=isnull(col1,0), col2=isnull(col2,0), col3=isnull(col3,0)
     where (col1 is null) or (col2 is null) or (col3 is null)

edited Feb 27 '14 at 12:29

answered Feb 27 '14 at 11:14

Vikram Jain

5,498
1
19
31

score 1 · Answer 3 · answered Feb 27 '14 at 12:40

You can create temp table with 3 more comuputed columns value of which is based on your current columns..

Example below..

CREATE TABLE #Products 
(
    ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
  , InventoryValue AS isnull(QtyAvailable,0)
)

insert into #Products(QtyAvailable,UnitPrice)
values (null,10),(20,10)

select * from #Products

In the above example InventoryValue is the computed column and value is populated based on QtyAvailable value..

Hope it helps!

score 0 · Answer 4 · answered Feb 27 '14 at 13:03

As Vikram says, after retrieving the data into your temp table you can then update the values accordingly. if it is just column3 that could be null then

UPDATE #temp
SET col3 = 0 
WHERE col3 IS NULL

will do this just fine.

Otherwise you could just do the ISNULL check when you select back from your temp table and use the information later on

SELECT ISNULL(col3, 0)
FROM #temp

Convert Stored procedure result from NULL to 0

4 Answers4