Stored Procedure Dynamic SQL stored result in variable

Question

I have A stored procedure written

DECLARE @AreaID AS INT
DECLARE @DayPrior AS INT
DECLARE @TableName AS VARCHAR(50)
DECLARE @StoreQuery AS NVARCHAR(MAX)
DECLARE @SQL_ExtractDlSql AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(2000)

SET @AreaID = 1
SET @DayPrior = 1
SET @TableName = 'Tbl_Lube'

SET @SQL_ExtractDlSql = 'SELECT Download_SQL From
                         HDDDataPackage.dbo.tbl_HDD_DataDownloadSetting
                         Where AreaId=@AreaID AND TableName=@TableName'

SET @ParameterDefinition = '@AreaID INT,@DayPrior INT,@TableName VARCHAR(50)'

EXECUTE sp_executesql @SQL_ExtractDlSql,
                      @ParameterDefinition,
                      @AreaID,
                      @DayPrior,
                      @TableName

PRINT @SQL_ExtractDlSql

I above Stored procedure, I get What I wanted on print, but instead of doing that, I wanted the selected data stores as a variable. Then what I done is I declared a variable @StoreSql and made some modified into the SQL statement show below:

Variable Declaration:

DECLARE @StoreSql AS NVARCHAR(MAX)

Modified the Code:

SET @SQL_ExtractDlSql = 'SELECT @StoreSql = Download_SQL From
                         HDDDataPackage.dbo.tbl_HDD_DataDownloadSetting
                         Where AreaId=@AreaID AND TableName=@TableName'

I executed the code, I get error Must declare the scalar variable "@StoreSql".

I have totally no idea how to deal with this.Any one can help.Beside,I am new to sqlserver,new to stored procedures.

Please don't hesitate to point out my any mistake,any..such as logic or etc. I learn from mistake.

Regards LiangCk

Martin Smith · Accepted Answer · 2011-10-20 10:25:12Z

1

The dynamic SQL string cannot reference variables from the outer scope. You need to declare it as an output parameter

SET @ParameterDefinition = '@AreaID INT,
                            @DayPrior INT,
                            @TableName VARCHAR(50), 
                            @StoreSql NVARCHAR(MAX) OUTPUT'

EXECUTE sp_executesql @SQL_ExtractDlSql,
                      @ParameterDefinition,
                      @AreaID,
                      @DayPrior,
                      @TableName,
                      @StoreSql OUTPUT

answered Oct 20, 2011 at 10:25

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Worgon Over a year ago

Thanks @Martin Smith,I know you mean already. I get my expected answer already.

Collectives™ on Stack Overflow

Stored Procedure Dynamic SQL stored result in variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related