sql query not printing

Question

Hi Guys,

I am trying to run a dynamic query but for some odd reason its not running. Its not even printing. Please can anyone tell me why is the below dynamic query not printing.

DECLARE @CLIENTPK_NEW AS VARCHAR(50)
DECLARE @CGNEEPK AS VARCHAR(50)
DECLARE @TYPE AS VARCHAR(10)

SET @CLIENTPK_NEW='6EF77AAA-1A7B-4D03-A448-D1088DED4134'
SET @CGNEEPK= NULL
SET @TYPE='Mag'

DECLARE @SQL NVARCHAR(MAX)       

SET @SQL = '    
SELECT       
PUBLISHER
FROM CLIENT_SHIPPINGREPORTDATA_FUNCTION('
  + @CLIENTPK_NEW + ' , ' 
  + @CGNEEPK + ' , ' 
  + @TYPE +' )' <=== This is the troubled line, but not sure what is error is.

PRINT  @SQL    <== **Why is this not priniting**

Many thanks

Answer 1

Change to

SET @CGNEEPK= '' 

Answer 2

You are adding string values to a null value (@CGNEEPK) which results in NULL. When you print NULL, you see nothing. You need to use ISNULL(@CGNEEPK, '') instead.

Answer 3

If you concatenate a NULL to a string in SQL, the whole string will be NULL. Change

SET @CGNEEPK= NULL

to

SET @CGNEEPK= ''

Answer 4

Sounds like a null-concatenation problem with @CGNEEPK as your setting it to NULL. For all of your paramteres as ISNULL to them, or set @CGNEEPK = ''

SET @SQL = '     
SELECT        
PUBLISHER 
FROM CLIENT_SHIPPINGREPORTDATA_FUNCTION(' 
  + ISNULL(@CLIENTPK_NEW, '') + ' , '  
  + ISNULL(@CGNEEPK, '') + ' , '  
  + ISNULL(@TYPE, '') +' )' 

PRINT  @SQL    <== **Why is this not priniting** 

Answer 5

As Noel said, it's because you're trying to concatenate a NULL into a VARCHAR - the end result will be NULL. You'd also need to enclose the other varchar values in single quotes to pass them in, which starts becoming messy/problematic.

Use parameterised TSQL instead. This will allow you to easily pass NULL into your function as well as helping protect against SQL injection.

DECLARE @CLIENTPK_NEW AS VARCHAR(50)
DECLARE @CGNEEPK AS VARCHAR(50)
DECLARE @TYPE AS VARCHAR(10)

SET @CLIENTPK_NEW='6EF77AAA-1A7B-4D03-A448-D1088DED4134'
SET @CGNEEPK= NULL
SET @TYPE='Mag'

DECLARE @SQL NVARCHAR(MAX)       

SET @SQL = '    
SELECT       
PUBLISHER
FROM CLIENT_SHIPPINGREPORTDATA_FUNCTION(
  @CLIENTPK_NEW, @CGNEEPK, @TYPE)'

-- Then to execute it:
EXECUTE sp_executesql @SQL, 
    N'@CLIENTPK_NEW VARCHAR(50), @CGNEEPK VARCHAR(50), @TYPE VARCHAR(10)', 
    @CLIENTPK_NEW, @CGNEEPK, @TYPE

Answer 6

After the null problem (as answerd by a lot of others), you might look into the @TYPE variable. That is a string ('Mag'), so you need quotes around that value and the other strings:

SET @SQL = '    
SELECT       
PUBLISHER
FROM CLIENT_SHIPPINGREPORTDATA_FUNCTION('''
  + @CLIENTPK_NEW + ''' , ''' 
  + isnull(@CGNEEPK, '') + ''' , ''' 
  + @TYPE +''' )'