CODEWITHSUNDEEP
sqlsql-server
Sheen
Sheen

Reputation: 3441

Convert Stored procedure result from NULL to 0

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

Upvotes: 1

Views: 3155

Answers (4)

Jamie Pollard
Jamie Pollard

Reputation: 1599

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

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

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!

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

 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)

Upvotes: 1

BAdmin
BAdmin

Reputation: 937

Give it a try,

ISNULL(COLUMN_NAME, 0)

Upvotes: 5

Related Questions