Show 0 Instead of Null In Dynamic SQL Stored Proc

Question

I asked something similar earlier, but had forgotten I had to move it to a stored procedure. How can I have this query show 0 instead of empty (NULL)?

  Create Table OrderData
  (
    id int IDENTITY(1,1) PRIMARY KEY,
    ordernumber varchar(250),
    productcode varchar(100),
    quantity int
  )

  Insert Into OrderData Values
  ('18012094', '36923_CC-CM-DUNK-S-SR-003', '12')
  ,('19068100', '36923_CC-TEA-HT169-S-SR-045', '1')
  ,('19068100', '36923_CCSC-MONJ16GR-S-SC-076', '1')

  Create Table ProdInfo
  (
     id int IDENTITY(1,1) PRIMARY KEY,
     sku varchar(100),
     vendorsku varchar(100)
  )

  Insert Into ProdInfo Values
  ('36923_CC-CM-DUNK-S-SR-003', 'IS1879')
  ,('36923_CC-TEA-HT169-S-SR-045','IS1880')
  ,('36923_CCSC-MONJ16GR-S-SC-076','IS1881')

And this is the stored proc with the dynamic sql

alter procedure dbo.GetTagVeraCoreWOrkbook
As

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
DECLARE @SQL_QUERY NVARCHAR(max)

SELECT @cols = @cols + QUOTENAME(vendorsku) + ',' FROM (select distinct vendorsku from ProdInfo ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

SET @SQL_QUERY = 
N'SELECT * from 
(
    select od.ordernumber, psi.vendorsku, od.quantity 
    from orderdata od
    inner join prodinfo psi
    on od.productcode = psi.sku
) src
pivot 
(
    max(quantity) for vendorsku in (' + @cols + ')
) piv'

EXECUTE sp_executesql @SQL_QUERY

Answer 1

Hi @Johnathan Simpson ,

You could use isnull function to show 0 Instead of Null mentioned by Viorel.

In addition, you could also consider to use COALESCE which is an alternative approach of ISNULL.

Please refer to below:

alter procedure dbo.GetTagVeraCoreWOrkbook  
 As  
  
 DECLARE @cols  AS NVARCHAR(MAX)='';  
 DECLARE @cols1  AS NVARCHAR(MAX)='';  
 DECLARE @query AS NVARCHAR(MAX)='';  
 DECLARE @SQL_QUERY NVARCHAR(max)  
      
 SELECT @cols = @cols + QUOTENAME(vendorsku) + ',' FROM (select distinct vendorsku from ProdInfo ) as tmp  
 select @cols = substring(@cols, 0, len(@cols)) --trim "," at end  
  
 SELECT @cols1 = @cols1 + ' COALESCE('+QUOTENAME(vendorsku)+',0) ' +QUOTENAME(vendorsku)+ ',' FROM (select distinct vendorsku from ProdInfo ) as tmp  
 select @cols1 = substring(@cols1, 0, len(@cols1))  
  
 SET @SQL_QUERY =   
 N'SELECT ordernumber,'+@cols1+' from   
 (  
     select od.ordernumber, psi.vendorsku, od.quantity   
     from orderdata od  
     inner join prodinfo psi  
     on od.productcode = psi.sku  
 ) src  
 pivot   
 (  
     max(quantity) for vendorsku in (' + @cols + ')  
 ) piv'  
        
 EXECUTE sp_executesql @SQL_QUERY  

Please refer to below link for more details about the difference.
Deciding between COALESCE and ISNULL in SQL Server

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try something like this:

. . .
declare @cols1 nvarchar(max) = 'ordernumber'
select @cols1 = @cols1 + ', isnull(' + QUOTENAME(vendorsku) + ',0) as ' + QUOTENAME(vendorsku) FROM (select distinct vendorsku from ProdInfo) as tmp

SET @SQL_QUERY = 
 N'SELECT ' + @cols1 + ' from 
. . .