CODEWITHSUNDEEP
azurestored-proceduresazure-sql-databaseazure-synapse
narayana kandukuri
narayana kandukuri

Reputation: 63

Assign query result to variable while passing table name as parameter in azure synapse stored procedure

Passing table name as parameter to Azure Synapse stored procedure and assign the query(use the passed parameter in SQL) result to variable.

Sample code below:

CREATE PROC [TEST].[DEMO_PROC] @TableName [VARCHAR](1000) AS
BEGIN
    DECLARE @count_variable INT;
    SELECT @count_variable=count(1) FROM @TableName
    PRINT @count_variable
END

ERROR: Msg 103010, Level 16, State 1, Line 5 Parse error at line: 39, column: 42: Incorrect syntax near '@TableName'.

I get an error message saying TableName cannot be assigned as a parameter, If I give a hardcoded table name instead of variable it works.

Upvotes: 0

Views: 2331

Answers (2)

narayana kandukuri
narayana kandukuri

Reputation: 63

It works by using sp_executesql: Below is the changed SP which is working.

CREATE PROC [TEST].[DEMO_PROC] @TableName VARCHAR AS 
BEGIN 

DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
DECLARE @count_variable INT;

SET @SQLString = 'SELECT @count_variable = count(1)   FROM '+@TableName ;  
SET @ParmDefinition = '@count_variable INT OUTPUT';  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  @count_variable=@count_variable OUTPUT;  

PRINT @count_variable;

END

Upvotes: 0

Joel Cochran
Joel Cochran

Reputation: 7758

You'll need to use dynamic SQL. Build the statement as a variable, concatenating the @TableName as appropriate, then pass the variable to EXEC:

DECLARE @sql VARCHAR(MAX)

IF OBJECT_ID(@TableName) IS NOT NULL
BEGIN
    SET @sql = 'DROP TABLE ' + @TableName;
    EXEC(@sql)
END

Upvotes: 0

Related Questions