Sql-server – dynamic sql – assigning variable a value from result of dynamic sql query

sql server

Been struggling with this one for a good while now. I'm trying to use sp_executesql below:

DECLARE @SQLString nvarchar(500);   
DECLARE @ParmDefinition nvarchar(500);  
DECLARE @IntVariable int;  
DECLARE @fulldbtablename nvarchar(500);  
DECLARE @ID bigint;  

SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';  
SET @ParmDefinition = N'@daystokeep int,  
@IDOut bigint OUTPUT';  
SET @IntVariable = -1; 

EXECUTE sp_executesql  
@SQLString  
,@ParmDefinition  
,@daystokeep = @IntVariable 
,@IDOut = @ID OUTPUT;

I'm getting the error Must declare the scalar variable "@ID"

I've reviewed the help below, but I can't shake this error. Has anyone any ideas? I am declaring @ID in DECLARE @ID bigint;

To help me debug, I split the query into a new window and ran the query below which completes successfully so I am pulling my hair out:

DECLARE @Databasename varchar(500)
DECLARE @Tablename varchar(500)
DECLARE @ID bigint;
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
DECLARE @IntVariable int;  
DECLARE @fulldbtablename nvarchar(500); 

SET @databasename = 'DatabaseName'
Set @Tablename = 'TableName'
SET @fulldbtablename = QUOTENAME(@databasename) + '.[DBO].' + QUOTENAME(@tablename)

SET @SQLString = N'SELECT TOP 1 @IDOut = ID FROM' + @fulldbtablename + 'WHERE LastModified < DATEADD(DAY, @daystokeep, GETDATE()) order by 1 desc';  
SET @ParmDefinition = N'@daystokeep int,  
@IDOut bigint OUTPUT';  
SET @IntVariable = -1; 

EXECUTE sp_executesql  
@SQLString  
,@ParmDefinition  
,@daystokeep = @IntVariable 
,@IDOut = @ID OUTPUT;  

SELECT @ID

Anyone any ideas?????? Thank you!

Best Answer

You were missing the variable assignment inside the dynamic SQL batch.

DECLARE @sqlcommand NVARCHAR(MAX) = 'SELECT TOP 1 @ID = (ID) --Assign me!
                                     FROM ' + QUOTENAME(@tablename) + ' 
                                     WHERE LastModified < DATEADD(DAY,' + @NumberOfKeepDays + ', GETDATE()) 
                                    ORDER BY 1 DESC';

EXECUTE sys.sp_executesql @sqlcommand, N'@ID BIGINT OUTPUT', @ID = @ID OUTPUT;

The example from the sp_executesql doc is pretty clear, though suffers from formatting.

DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
DECLARE @SalesOrderNumber nvarchar(25);  
DECLARE @IntVariable int;  

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber) --Assign me!
    FROM Sales.SalesOrderHeader  
    WHERE CustomerID = @CustomerID';  
SET @ParmDefinition = N'@CustomerID int,  
    @SalesOrderOUT nvarchar(25) OUTPUT';  
SET @IntVariable = 22276;  

EXECUTE sp_executesql  
    @SQLString  
    ,@ParmDefinition  
    ,@CustomerID = @IntVariable  
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;  
-- This SELECT statement returns the value of the OUTPUT parameter.  
SELECT @SalesOrderNumber;

If you want to learn a whole heck of a lot about dynamic SQL, SQL injection (and you should, because what you're executing now is potentially unsafe), head over here

Related Question