CODEWITHSUNDEEP
sqlsql-serverstored-procedures
blankon91
blankon91

Reputation: 515

How to use variables for parameter in execute a stored procedure?

I've declared and set some variables. Then I want to use that variable value as a parameter in executing a stored procedure. But I always get error

Must declare the scalar variable.

Here are my variables:

--declare variables for parameter @listweek
declare @tgl_other varchar(50);

--set variables for parameter @listweekset 
set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);

--the variable values should be like this 30-(01-03-2012 - 08-03-2012) 
--and this value that I want to use as parameter value in executing stored procedure

and this is the executing stored procedure:

insert into my_table (field1, field2, field3)
EXEC my_store_procedure @tgl_other
-- the parameter for my_store_procedure is like '30-(01-03-2012 - 08-03-2012)'

Is it my variables not correct? or I've wrong way to use variable for parameter?

UPDATE:

if I execute this query:

declare @tgl_other varchar(50);
set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);
EXEC my_store_procedure @tgl_other

it works fine, but if I add INSERT INTO.. statements it won't work. Why it happens?


UPDATE II: here is the whole query that I try to run

declare @tgl_other varchar(50);
set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);
TRUNCATE TABLE mytable
GO
INSERT INTO mytable (field1, field2)    
EXEC my_store_procedure @tgl_other
GO

Upvotes: 1

Views: 13981

Answers (1)

marc_s
marc_s

Reputation: 754258

DO you have a GO somewhere between declaring your variables and the call to execute the stored procedure? Variables are only visible inside their scope - when you have a GO in there, that terminates the previous batch/scope and starts a new one - and in that new one, the variables aren't visible/declared anymore..

So try this instead:

declare @tgl_other varchar(50);

set @tgl_other = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash where year(dates) = year(getdate()) order by numweek desc);

TRUNCATE TABLE mytable

-- GO     remove this GO to make it work!

INSERT INTO mytable (field1, field2)    
EXEC my_store_procedure @tgl_other
GO

Upvotes: 5

Related Questions