|
|
|
Valued Member
 
Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 52,
Visits: 92
|
|
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
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 4,975,
Visits: 3,921
|
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 52,
Visits: 92
|
|
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.
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 4,975,
Visits: 3,921
|
|
- 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 + ' ...
 Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere 
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 148,
Visits: 360
|
|
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.
Converting oxygen into carbon dioxide, since 1955.
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 52,
Visits: 92
|
|
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.
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 4,975,
Visits: 3,921
|
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 11, 2010 12:26 PM
Points: 116,
Visits: 17
|
|
To keep you variables alive, sp_executesql could be preferable to EXEC.
Kindest Regards,
M Suresh Kumar
|
|
|
|
