Cannot store result into variable SQL

Christopher

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.

:-)

M.Ali
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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Store a sql query result in pentaho variable

From Dev

How to store the result of dynamic SQL in a variable?

From Dev

How to store the result of a SQL statement as a variable and use the result in an SSIS Expression?

From Dev

Store column result in variable

From Dev

Store function result into variable

From Dev

Store the result of a command in a variable

From Dev

store into variable a sudo result

From Dev

Store function result into variable

From Dev

How to store select statement result to table variable in sql server

From Dev

How to store result of stored procedure in a variable using SQL Server

From Dev

Store the first result value into a variable from sql query in php

From Dev

Trying to store result in xml format in variable sql server

From Dev

Store the first result value into a variable from sql query in php

From Dev

How to store SQL query result in a variable using PHP?

From Dev

Store the assert failure result in a variable

From Dev

Store the result of a Dynamic Query in a variable

From Dev

Store into variable result of function VBA

From Dev

Store cmd Find result to a variable

From Dev

Sed Fails to store the result into a variable

From Dev

Store Result of SVN Command in Variable

From Dev

How to store a query result in a variable

From Dev

I have an SQL table. With an SQL request with PHP I want store the result of this request in a variable.

From Dev

Store expression in a variable in SQL

From Dev

Grep a variable and store the result in a vector in R

From Dev

Is it okay to store the result of a JQuery selector in a variable?

From Dev

How to store query result (a single document) into a variable?

From Dev

store result of select query into array variable

From Dev

store the result of xpath into an variable to assist in future query

From Dev

Cut of word from a string and store result to variable

Related Related

  1. 1

    Store a sql query result in pentaho variable

  2. 2

    How to store the result of dynamic SQL in a variable?

  3. 3

    How to store the result of a SQL statement as a variable and use the result in an SSIS Expression?

  4. 4

    Store column result in variable

  5. 5

    Store function result into variable

  6. 6

    Store the result of a command in a variable

  7. 7

    store into variable a sudo result

  8. 8

    Store function result into variable

  9. 9

    How to store select statement result to table variable in sql server

  10. 10

    How to store result of stored procedure in a variable using SQL Server

  11. 11

    Store the first result value into a variable from sql query in php

  12. 12

    Trying to store result in xml format in variable sql server

  13. 13

    Store the first result value into a variable from sql query in php

  14. 14

    How to store SQL query result in a variable using PHP?

  15. 15

    Store the assert failure result in a variable

  16. 16

    Store the result of a Dynamic Query in a variable

  17. 17

    Store into variable result of function VBA

  18. 18

    Store cmd Find result to a variable

  19. 19

    Sed Fails to store the result into a variable

  20. 20

    Store Result of SVN Command in Variable

  21. 21

    How to store a query result in a variable

  22. 22

    I have an SQL table. With an SQL request with PHP I want store the result of this request in a variable.

  23. 23

    Store expression in a variable in SQL

  24. 24

    Grep a variable and store the result in a vector in R

  25. 25

    Is it okay to store the result of a JQuery selector in a variable?

  26. 26

    How to store query result (a single document) into a variable?

  27. 27

    store result of select query into array variable

  28. 28

    store the result of xpath into an variable to assist in future query

  29. 29

    Cut of word from a string and store result to variable

HotTag

Archive