Select stored proc parameters in dynamic code

MyleneChalut 136 Reputation points
2021-02-23T13:05:32.123+00:00

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

0 comments No comments
{count} votes

Accepted answer
  1. MyleneChalut 136 Reputation points
    2021-02-23T18:34:05.127+00:00
        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
    

3 additional answers

Sort by: Most helpful
  1. Viorel 95,156 Reputation points
    2021-02-23T13:50:37.13+00:00

    Try this fix:

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

  2. Erland Sommarskog 78,746 Reputation points MVP
    2021-02-23T23:05:07.46+00:00

    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.

    0 comments No comments

  3. Erland Sommarskog 78,746 Reputation points MVP
    2021-02-24T22:21:51.66+00:00

    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'