Select stored proc parameters in dynamic code

Question

Hi guys !!

This question may sound stupid... but hey ! I can't find the answer ! lol!!

I have this query that I need to convert to dynamic query:

INSERT INTO [SurveyInterface].[tblCEProcessingWorkingLCPSUF](  
					 ParentId,  
					 OperatingEntityNumber,  
					 SurveyGroupCodeId,  
					 ReferencePeriod,  
					 SurveyCodeId,  
					 StructureLevel )  
	SELECT ParentId,  
		    RealOperatingEntityNumber AS OperatingEntityNumber,  
		    '71' AS SurveyGroupCodeId,  
		    '201906' AS ReferencePeriod,  
		    'ZM' AS SurveyCodeId,  
		    StructureLevel  
	 FROM  dbo.tblSUFGeneric201906  
	 WHERE ParentId <> RealOperatingEntityNumber;  

It looks so simple !

DECLARE @InputSurveyGroupCodeId INT = 71  
               ,@InputReferencePeriod  VARCHAR(6) = '201906'  
               ,@InputSurveyCodeId VARCHAR(2) = 'ZM'  
  
EXEC ('INSERT INTO [SurveyInterface].[tblCEProcessingWorkingLCPSUF](  
					 ParentId,  
					 OperatingEntityNumber,  
					 SurveyGroupCodeId,  
					 ReferencePeriod,  
					 SurveyCodeId,  
					 StructureLevel )  
	   SELECT ParentId,  
			RealOperatingEntityNumber AS OperatingEntityNumber,  
			  ' + @InputSurveyGroupCodeId + ' AS SurveyGroupCodeId,  
			  ' + @InputReferencePeriod + ' AS ReferencePeriod,  
			  ' + @InputSurveyCodeId + ' AS SurveyCodeId,  
			  StructureLevel  
	 FROM  ' + @InputSUFFrameTable + '  
	 WHERE ParentId <> RealOperatingEntityNumber')  

But when I execute it, I get the folowing error message :
Msg 50000, Level 16, State 2, Procedure uspCEProcessingMainScriptAllProcesses, Line 634 [Batch Start Line 2]
Invalid column name 'ZM'.

Why is it working when the values are numbers, but not string ?

Thanks a lot in advance for your help :-)
Mylene

Answer 1

    DECLARE @sqlstatement NVARCHAR(max)
    SET @sqlstatement = 'INSERT INTO [SurveyInterface].tblCEProcessingWorkingLCPSUF](
            ParentId,
            OperatingEntityNumber,
            SurveyGroupCodeId,
            ReferencePeriod,
            SurveyCodeId,
            StructureLevel )
        SELECT ParentId,
               RealOperatingEntityNumber AS OperatingEntityNumber,
              ' + quotename(@InputSurveyGroupCodeId, '''') + ' AS SurveyGroupCodeId,
              ' + quotename(@InputReferencePeriod, '''') + ' AS ReferencePeriod,
              ' + quotename(@InputSurveyCodeId, '''') + ' AS SurveyCodeId,
               StructureLevel
        FROM  ' + @InputSUFFrameTable + '
        WHERE ParentId <> RealOperatingEntityNumber'

    PRINT @sqlstatement
    EXEC sys.sp_executesql @sqlstatement

Answer 2

Try this fix:

. . .
' + quotename(@InputSurveyGroupCodeId, '''') + ' AS SurveyGroupCodeId,
' + quotename(@InputReferencePeriod, '''') + ' AS ReferencePeriod,
' + quotename(@InputSurveyCodeId, '''') + ' AS SurveyCodeId,
. . .
FROM  ' + quotename(@InputSUFFrameTable) + '
. . .

Answer 3

It looks so simple !

And that is the biggest curse with dynamic SQL. It looks simple. It isn't.

Dynamic SQL can be a blessing when use for the right thing. Here it seems that the desire to use dynamic SQL is a dynamic table name. This is often due to a poor design, and with a better design, you would not need dynamic SQL at all.

On my web site you can find the article The Curse and Blessings of Dynamic SQL where you can learn more.

Answer 4

Horrors! I only saw the dynamic table name and did not pay attention to the inlined string parameters. That's an absolute no-no for several reasons.

SELECT @sql= 'INSERT INTO [SurveyInterface].[tblCEProcessingWorkingLCPSUF](
                          ParentId,
                          OperatingEntityNumber,
                          SurveyGroupCodeId,
                          ReferencePeriod,
                          SurveyCodeId,
                          StructureLevel )
            SELECT ParentId,
                 RealOperatingEntityNumber AS OperatingEntityNumber,
                   @InputSurveyGroupCodeId  AS SurveyGroupCodeId,
                   @InputReferencePeriod  AS ReferencePeriod,
                   @InputSurveyCodeId +  AS SurveyCodeId,
                   StructureLevel
          FROM  ' + quotename(@InputSUFFrameTable) + '
          WHERE ParentId <> RealOperatingEntityNumber'

EXEC sp_executesql @sql, 
        N' @InputSurveyGroupCodeId INT
           ,@InputReferencePeriod  VARCHAR(6)
           ,@InputSurveyCodeId VARCHAR(2)',  71, '201906', 'ZM'