Using variable inside dynamic SQL

Question

I have the below SQL..What I am trying to do is use the Parameter defined at the stored procedure level inside dynamic SQL:

CREATE PROCEDURE [dbo].[Test] 
    (@DealID NVARCHAR(500),
     @OUTPUT NVARCHAR(MAX) OUTPUT,    
     @FeeType CHAR(1)
    )  --  I want to use this parameter inside dynamic SQL query
AS    
    DECLARE @exec_str NVARCHAR(MAX)
    DECLARE @ParmDefinition NVARCHAR(MAX)
BEGIN
    SET @exec_str = N'DECLARE @ParmDefinition NVARCHAR(500)
                      SELECT * FROM @FeeType'    --This is where I want to use the variable

    DECLARE @ParamDefinition nvarchar(max)
    SET @ParamDefinition = N'@OUTPUT NVARCHAR(MAX) OUTPUT'

    EXEC sp_executesql @exec_str, @ParamDefinition

Can someone please tell me how to do it?

Thanks

You cannot use a parameter for an identifier, such as a table name, alas. — Gordon Linoff, Aug 08 '17 at 16:10
Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — Tab Alleman, Aug 08 '17 at 16:14

score 3 · Answer 1 · answered Aug 08 '17 at 16:18

In SQL Server Identifiers can't be parameterized.
Since you are using dynamic SQL anyway, you can do something like this:

SET @exec_str= N'Select * from '+ @FeeType 

EXEC(@exec_str)

However, this is vulnerable to SQL injection attacks. To reduce the risk to minimum you should check first that such a table name exists, and I would also use quotename just to be on the safe side:

IF EXISTS
(
    SELECT 1
    FROM Information_Schema.Tables
    WHERE TABLE_NAME = @FeeType
)
BEGIN
    SET @exec_str= N'Select * from '+ QUOTENAME(@FeeType)
    EXEC(@exec_str)
END

Using variable inside dynamic SQL

1 Answers1