February 9, 2010 at 7:45 am
If possible I need some assistance on a Stored Procedure.
I am trying to insert a cursor value into a table that is a genrating a dynamic SQL. (I know not the best coding procedure)
But everytime I try to insert the cursor value I get a "Must Declare The Scalar Value". The variable is declared..
Here is my wacky code:
The issue lies in the set @sql1...I am inserting @fiscal_month_nm which is a cursor value.
ALTER PROCEDURE [dbo].[Turn_prod_Month2]
@curmth varchar (400),
@curyr int
AS
-- Create temporary tables
CREATE TABLE #tmp_level1_sales
(level1 varchar(600),
cost_amt money,
fm varchar(50))
CREATE TABLE #tmp_level1_inv
(level1 varchar(600),
avgvalue money,
fm varchar(50))
-- Declare Variables
declare
@sql varchar (8000),
@sql2 varchar (8000),
@currentdt smalldatetime,
@enddt smalldatetime,
@enddt2 varchar (10),
@startdt smalldatetime,
@startdt2 varchar (10),
@fiscal_month int,
@fiscal_month_nm varchar(50),
@fiscal_year int
declare cde cursor for
select distinct fiscal_month, nbr_name from d_dates where cal_year = @curyr and fiscal_month <= @curmth
open cde
fetch next from cde into @fiscal_month, @fiscal_month_nm
while (@@FETCH_STATUS = 0)
begin
-- Set variables
Select @currentdt = max (dt) from d_dates where fiscal_month = @fiscal_month and fiscal_year = @curyr -- get max date for month and year variables (Variables passed from Media)
Select @enddt = convert(varchar,dt,111) from d_dates where dt = @currentdt -- set end date.
Select @startdt = convert(varchar,dt,111) from d_dates where dt = dateadd(mm,-12,@enddt) -- set start date. 12 months prior
Select @startdt2 = convert(varchar,@startdt,111) -- strip out time
Select @enddt2 = convert(varchar,@enddt,111) -- strip out time
set @sql = 'SELECT F_Turns_cogs.level1,
SUM(F_Turns_cogs.cogs),@fiscal_month_nm
FROM
F_Turns_cogs,
D_Dates
WHERE
(D_Dates.date_id = F_Turns_cogs.date_id)'
set @sql2 = 'SELECT F_Turns_value.level1,
SUM(F_Turns_value.value) / count(distinct D_Dates.date_id),@fiscal_month_nm
FROM
F_Turns_value,
D_Dates
WHERE
(D_Dates.date_id = F_Turns_value.date_id)'
--Populate temp tables with 12 months worth of data
INSERT INTO #tmp_level1_sales (level1, cost_amt, fm)
exec (@Sql + ' AND ' + ' D_Dates.dt >= ' + ''''+ @startdt2 + '''' + ' AND ' + ' D_Dates.dt <= ' + ''''+ @enddt2 + '''' + ' GROUP BY F_Turns_cogs.level1')
INSERT INTO #tmp_level1_inv (level1, avgvalue,fm)
exec (@Sql2 + ' AND ' + ' D_Dates.dt >= ' + ''''+ @startdt2 + '''' + ' AND ' + ' D_Dates.dt <= ' + ''''+ @enddt2 + ''''+ ' GROUP BY F_Turns_value.level1')
fetch next from cde into @fiscal_month, @fiscal_month_nm
end
-- Generate Result Set
select #tmp_level1_sales.fm as Month, #tmp_level1_sales.level1 as Dept, #tmp_level1_sales.cost_amt / #tmp_level1_inv.avgvalue as Turns
from
#tmp_level1_sales,
#tmp_level1_inv
where #tmp_level1_sales.level1 = #tmp_level1_inv.level1
and #tmp_level1_sales.fm = #tmp_level1_inv.fm
and #tmp_level1_inv.avgvalue <> 0
order by 1
close cde
deallocate cde
February 9, 2010 at 7:49 am
Your Set SQL1 is using @fiscal_month_nm in the sting itself !
That's causing the "must declare varriable" error
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2010 at 7:54 am
Thanks for the quick reply.
I knew where the error was, I just don't understand why the error is happening?
I a little baffled.
February 9, 2010 at 8:08 am
- You execute the @SQL1. This runs by its own batch and doesn't know your variable.
- IMO you should just change your Set @SQL1 so it uses
... ' + @yourvar + ' ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2010 at 8:15 am
Your dynamic SQL creates a new session that knows nothing about the delcared variables you set in your "normal" session. You will need to declare and set you variables within you dynamic SQL expression.
February 9, 2010 at 8:34 am
Thanks the ... + @var + ... worked.
I haved it in my code and dont know why I just didnt think of it before.
I guess I was looking at it in only one way.
Thanks for the help.
February 9, 2010 at 12:12 pm
Been there ..... done that ...:hehe:
Learn to play .... play to learn:w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2010 at 12:06 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply