Dynamic sql error in Select when empty string pass in sql server 2016

Question

I am very new to dynamic sql. SQL SERVER 2016

The below code is written using dynamic sql, unfortunately throwing error. please help.

CREATE PROCEDURE [USA_PHILIPS].[usp_stock] 
@VCM int,
@ID varchar(25), --Always define your varchar lengths
@SCHEMA_NAME sysname --Correct data type for object names

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10)

    SET @SQL = N'DROP TABLE IF EXISTS ' + QUOTENAME(@SCHEMA_NAME) + N'.[stock];' + @CRLF + @CRLF +
               N'SELECT STOCKNUMBER,STOCKBOOKS,'No' as ISCHANGEMANAGEMENTACTIVATED, 0 as PERSONANNIVERSARYDAY,  '' as NEWCOL,'Normal' as DEFAULTCASHDISCOUNTUSAGE',CASE WHEN VENDOR=23490 then '30-INTERCO' END AS VENDID + @CRLF +
               N'INTO ' + QUOTEMANE(@SCHEMA_NAME) + N'.[stock]' + @CRLF +
               N'FROM [USA_PHILIPS].[DMARTSTOCK]' + @CRLF +
               N'WHERE VCM=@VCM' + @CRLF +
               N'  AND ID = @ID;';

    EXEC sys.sp_executesql @SQL, N'@VCM int, @ID varchar(25)', @VCM, @ID;

END;

Not sure, I am getting error in select statement.

Answer 1

Try this fix:

SET @SQL = N'DROP TABLE IF EXISTS ' + QUOTENAME(@SCHEMA_NAME) + N'.[stock];' + @CRLF + @CRLF +  
        N'SELECT STOCKNUMBER,STOCKBOOKS,''No'' as ISCHANGEMANAGEMENTACTIVATED,    0 as PERSONANNIVERSARYDAY, '''' as NEWCOL,''Normal'' as DEFAULTCASHDISCOUNTUSAGE, CASE WHEN VENDOR=23490 then ''30-INTERCO'' END AS VENDID' + @CRLF +  
        N'INTO ' + QUOTENAME(@SCHEMA_NAME) + N'.[stock]' + @CRLF +  
        N'FROM [USA_PHILIPS].[DMARTSTOCK]' + @CRLF +  
        N'WHERE VCM=@VCM' + @CRLF +  
        N'  AND ID = @ID;';  



  

Answer 2

Just use the following statement to see what is wrong:

print @SQL;

And after that try to execute print's output as-is without it being a dynamic SQL.

Answer 3

You don't need to put all those end of line characters (@CRLF) when building the string. You can do something like the following:

Declare @VCM int = 10
      , @ID varchar(25) = 'SomeIDValue'
      , @SCHEMA_NAME sysname = 'test';

Declare @SQL nvarchar(max) = '';

    Set @SQL = @SQL + '

   Drop Table If Exists ' + quotename(@SCHEMA_NAME) + '.[stock];

 Select STOCKNUMBER
      , STOCKBOOKS
      , ''No'' As ISCHANGEMANAGEMENTACTIVATED
      , 0 As PERSONANNIVERSARYDAY
      , '''' As NEWCOL
      , ''Normal'' As DEFAULTCASHDISCOUNTUSAGE
      , CASE WHEN VENDOR=23490 then ''30-INTERCO'' END AS VENDID
   INTO ' + quotename(@SCHEMA_NAME) + '.[stock]
   FROM [USA_PHILIPS].[DMARTSTOCK]
  WHERE VCM = @VCM
    AND ID = @ID;';

  Print @SQL;

This makes it much easier to read - and that means it is much easier to manage and maintain. You could also make it simpler by doing something like this:

Declare @VCM int = 10
      , @ID varchar(25) = 'SomeIDValue'
      , @SCHEMA_NAME sysname = 'test';

Declare @SQL nvarchar(max) = ''
      , @tableReference sysname = concat(quotename(@SCHEMA_NAME), '.', quotename('stock'));

    Set @SQL = replace('

   Drop Table If Exists ##table_reference##;

 Select STOCKNUMBER
      , STOCKBOOKS
      , ''No'' As ISCHANGEMANAGEMENTACTIVATED
      , 0 As PERSONANNIVERSARYDAY
      , '''' As NEWCOL
      , ''Normal'' As DEFAULTCASHDISCOUNTUSAGE
      , CASE WHEN VENDOR=23490 then ''30-INTERCO'' END AS VENDID
   INTO ##table_reference##
   FROM [USA_PHILIPS].[DMARTSTOCK]
  WHERE VCM = @VCM
    AND ID = @ID;', '##table_reference##', @tableReference);

  Print @SQL;