just have a general question today. I am trying to store the result in a variable however it's not working. I am not trying to do anything fancy rather a simple task. See below:
declare @prizeid bigint;
declare @today datetime;
declare @dayOfMonth int;
declare @year int;
declare @month int;
select @today = getdate();
select @dayOfMonth = Day(@today);
select @year = Year(@today);
select @month = Month(@today);
if @month = 1
begin
select @month = 12
select @year = @year - 1
end
else select @month = @month - 1;
declare @sqlQuery varchar(250);
declare @quantityForSnapShot bigint;
declare @filename varchar(25);
set @prizeid=31
set @filename = 'Prizes_' + REPLACE(STR(@month, 2, 0), ' ', '0') + '_' + ltrim(str(@year));
select @sqlQuery = 'select Quantity from ' + @filename +
' where PrizeID=' + convert(varchar,@prizeid)
EXEC @quantityForSnapShot=@sqlQuery
print @quantityForSnapShot
All I really want is to retreive the Quantity and store it in the var @quantityForSnapShot.
:-)
declare @prizeid bigint;
declare @today datetime;
declare @dayOfMonth int;
declare @year int;
declare @month int;
select @today = getdate();
select @dayOfMonth = Day(@today);
select @year = Year(@today);
select @month = Month(@today);
if (@month = 1)
begin
select @month = 12
select @year = @year - 1
end
else
begin
select @month = @month - 1;
end
declare @sqlQuery nvarchar(MAX); --<-- to be on safe side
declare @quantityForSnapShot bigint;
declare @filename varchar(25);
set @prizeid=31
set @filename = 'Prizes_' + REPLACE(STR(@month, 2, 0), ' ', '0') + '_' + ltrim(str(@year));
select @sqlQuery = N' select @quantityForSnapShot = Quantity ' +
N' from ' + QUOTENAME(@filename) +
N' where PrizeID = @prizeid'
EXECUTE sp_executesql @sqlQuery
,N'@prizeid bigint, @quantityForSnapShot bigint OUTPUT'
,@prizeid , @quantityForSnapShot OUTPUT
SELECT @quantityForSnapShot
You are trying to call this Dynamic sql as it were a stored procedure with a return value. You will need to use an OUTPUT parameter to retrieve the value of @quantityForSnapShot
variable from your dynamic sql.
Also I have used QUOTENAME Function to put square brackets []
around the table name, to tell sql server explicitly that it is an object name. A good practice to get in as it can protect you from Sql injection attack.
Also use system stored procedure sp_executesql
to execute dynamic sql.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments