toddpotter
asked on
SQL Server 2008 - Using Local Variable in Where Clause of Stored Procedure
I am new to sql stored procedures and I need help understanding how local variables can be (or should be) used. In the attached stored procedure I am passing in a parameter @Period. Then, I have declared 13 variables to store the current Period and past 12 periods. Then I want to use some (for now, @Period1 and @Period13) in the where clause of the returning select statement. I think by doing this it is called Dynamic SQL, which from what I read is not the best method. So, I switched to adding @Period1 thru @Period13 into a Temp Table and trying to use the results from the temp table (Period1 and Period13) in my where clause. Either way (using the local variables @Period1 and @Period13 or selecting the Period1 and Period13 from the temp table) my proc is returning 0 results. I know that the variables are causing the problem because if I take the variables out of the equation and just add 201001 and 201012 in my where clause everything works fine. I'm looking to understand the best way to use the local variables and how to get my proc to work.
Thanks in advance.
Thanks in advance.
USE [CoXXX]
GO
/****** Object: StoredProcedure [dbo].[rpt_cuTP_ExpenseAnalysis] Script Date: 03/23/2011 06:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
go
ALTER PROCEDURE [dbo].[rpt_cuTP_ExpenseAnalysis]
@Period INTEGER
AS
Declare
@Period1 integer,
@Period2 integer,
@Period3 integer,
@Period4 integer,
@Period5 integer,
@Period6 integer,
@Period7 integer,
@Period8 integer,
@Period9 integer,
@Period10 integer,
@Period11 integer,
@Period12 integer,
@Period13 integer
Begin
IF EXISTS
(
SELECT
*
FROM
tempdb..sysobjects
WHERE
id=OBJECT_ID('tempdb..#cuTempPeriod')
)
DROP TABLE #cuTempPeriod
IF EXISTS
(
SELECT
*
FROM
sysobjects
WHERE
id=OBJECT_ID('#Results')
)
DROP TABLE #Results
IF right(cast((@period)as varchar(6)),2) = '01'
begin
set @Period1 = @Period
set @Period2 = @Period1-89
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '02'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-89
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '03'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-89
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '04'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-89
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '05'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-89
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '06'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-89
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '07'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-89
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '08'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-89
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '09'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-89
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '10'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-89
set @Period12 = @Period11-1
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '11'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-89
set @Period13 = @Period12-1
end
Else if right(cast((@period)as varchar(6)),2) = '12'
begin
set @Period1 = @Period
set @Period2 = @Period1-1
set @Period3 = @Period2-1
set @Period4 = @Period3-1
set @Period5 = @Period4-1
set @Period6 = @Period5-1
set @Period7 = @Period6-1
set @Period8 = @Period7-1
set @Period9 = @Period8-1
set @Period10 = @Period9-1
set @Period11 = @Period10-1
set @Period12 = @Period11-1
set @Period13 = @Period12-89
end
Else set @Period = @Period
--Create Table to store the Periods
Create Table #cuTempPeriod(
Period1 int, Period2 int, Period3 int, Period4 int, Period5 int, Period6 int, Period7 int, Period8 int, Period9 int, Period10 int, Period11 int, Period12 int, Period13 int)
--Insert the Periods
Insert into #cuTempPeriod(Period1, Period2, Period3, Period4, Period5, Period6, Period7, Period8, Period9, Period10, Period11, Period12, Period13) values (@Period1,@Period2, @Period3, @Period4, @Period5, @Period6, @Period7, @Period8, @Period9, @Period10, @Period11, @Period12, @Period13)
--select @Period1 as Period1 ,@Period2 as Period2, @Period3 as Period3, @Period4 as Period4, @Period5 as Period5, @Period6 as Period6, @Period7 as Period7, @Period8 as Period8, @Period9 as Period9, @Period10 as Period10, @Period11 as Period11, @Period12 as Period12, @Period13 as Period13
create table #Results(Period int, TypeIS varchar(50), Amount money, AccountID int, AccountName varchar(50), AccountNumber varchar(50), DeptName varchar(50))
Insert into #Results(Period, TypeIS, Amount, AccountID, AccountName, AccountNumber, DeptName)
select j.Period, gat.TypeIS, SUM(det.amount) as Amount, ga.AccountID,ga.AccountName,ga.AccountNumber, dept.DeptName
from GLJournalDetails det
left JOIN GLJournal j ON j.JournalID=det.JournalID
left join GLDepts dept on dept.DeptID = det.DeptID
left join GLAccounts ga on ga.AccountID = det.GLID
left join GLAccountTypes gat on gat.AccountTypeID = ga.AccountTypeID
where j.Period between (select Period1 from #cuTempPeriod) and (select Period13 from #cuTempPeriod) and gat.TypeIS in ('Operating Expenses', 'Other Expenses')
group by j.Period,gat.TypeIS, dept.DeptName, dept.DeptID, ga.AccountID,ga.AccountName, ga.AccountNumber
order by j.period, ga.AccountID, dept.DeptName
select * from #Results
--exec(@sql)
--drop temp table
--Drop table #cuTempPeriod
--Drop table #Results
End
also why you are doing this conversion?
IF right(cast((@period)as varchar(6)),2) = '01'
you input variable is integer - why you can not do
if @period=1
IF right(cast((@period)as varchar(6)),2) = '01'
you input variable is integer - why you can not do
if @period=1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Period is the date (stored in database as integer YYYYMM). I pull out the '01' to determine January, '02' February, '03' March, etc.
I know that I can use @Period (the parameter) in the select statement but if I wanted to use a local variable in the where clause, how can I?
I know that I can use @Period (the parameter) in the select statement but if I wanted to use a local variable in the where clause, how can I?
2 questions
(1) Is this correct that GLJournal.period is Integer that looks like 201101, 200902, etc?
(2) In your procedure, when you pass 5 into it as period, what rows from GLJournal you want to select?
Only latest data or full year?
for example
1 means select * from GLJournal where period between 201001 and 201101
2 - means select * from GLJournal where period between 201002 and 201102
3, 4, etc - means
select * from GLJournal where period between 201003 and 201103
or
you alwasy need full year
select * from GLJournal where period between 200904 and 201004
(1) Is this correct that GLJournal.period is Integer that looks like 201101, 200902, etc?
(2) In your procedure, when you pass 5 into it as period, what rows from GLJournal you want to select?
Only latest data or full year?
for example
1 means select * from GLJournal where period between 201001 and 201101
2 - means select * from GLJournal where period between 201002 and 201102
3, 4, etc - means
select * from GLJournal where period between 201003 and 201103
or
you alwasy need full year
select * from GLJournal where period between 200904 and 201004
ASKER
1. yes
2. If 201101 gets passed in I will want 201001 to 201101. If 201102 gets passed in I will want 201002 to 201002. - I can get this by setting the j.Period between j.Period between @period -100 and @period
2. If 201101 gets passed in I will want 201001 to 201101. If 201102 gets passed in I will want 201002 to 201002. - I can get this by setting the j.Period between j.Period between @period -100 and @period
You still did not answer
3, 4, etc - means
select * from GLJournal where period between 201003 and 201103
or
you alwasy need full year
select * from GLJournal where period between 200904 and 201004
----
your SP
----------------------
create PROCEDURE [dbo].[rpt_cuTP_ExpenseAna
@Period INTEGER
AS
begin
declare @fromPeriod integer, @toPeriod integer
select @toPeriod = datepart(year,getdate())+@
select @fromPeriod = @toPeriod-100
-- this is for debugging, remove this print later
print @toPeriod
print @fromPeriod
select j.Period, gat.TypeIS, SUM(det.amount) as Amount, ga.AccountID,ga.AccountNam
from GLJournalDetails det
left JOIN GLJournal j ON j.JournalID=det.JournalID and j.Period between @fromPeriod and @toPeriod -100
left join GLDepts dept on dept.DeptID = det.DeptID
left join GLAccounts ga on ga.AccountID = det.GLID
left join GLAccountTypes gat on gat.AccountTypeID = ga.AccountTypeID and gat.TypeIS in ('Operating Expenses', 'Other Expenses')
group by j.Period,gat.TypeIS, dept.DeptName, dept.DeptID, ga.AccountID,ga.AccountNam
order by j.period, ga.AccountID, dept.DeptName
end
----------------------
3, 4, etc - means
select * from GLJournal where period between 201003 and 201103
or
you alwasy need full year
select * from GLJournal where period between 200904 and 201004
----
your SP
----------------------
create PROCEDURE [dbo].[rpt_cuTP_ExpenseAna
@Period INTEGER
AS
begin
declare @fromPeriod integer, @toPeriod integer
select @toPeriod = datepart(year,getdate())+@
select @fromPeriod = @toPeriod-100
-- this is for debugging, remove this print later
print @toPeriod
print @fromPeriod
select j.Period, gat.TypeIS, SUM(det.amount) as Amount, ga.AccountID,ga.AccountNam
from GLJournalDetails det
left JOIN GLJournal j ON j.JournalID=det.JournalID and j.Period between @fromPeriod and @toPeriod -100
left join GLDepts dept on dept.DeptID = det.DeptID
left join GLAccounts ga on ga.AccountID = det.GLID
left join GLAccountTypes gat on gat.AccountTypeID = ga.AccountTypeID and gat.TypeIS in ('Operating Expenses', 'Other Expenses')
group by j.Period,gat.TypeIS, dept.DeptName, dept.DeptID, ga.AccountID,ga.AccountNam
order by j.period, ga.AccountID, dept.DeptName
end
----------------------
ASKER
I always need the full year.
The above Proc cannot be correct. It is returning all Null's for the period and the TypeIS is returning null also.
The above Proc cannot be correct. It is returning all Null's for the period and the TypeIS is returning null also.
I used your original sql where you have
...
from GLJournalDetails det
left JOIN GLJournal j ..
...
which means you want ALL rows from GLJournalDetails table, regardless of if you have data in GLJournal table for given period or not
I do not know your DB design.
try to remove "left" join
If you can write SQL that will return your correct data for Period 4, I guess it means for April 2009 - April 2010?
I can help you with proc that use parameters
...
from GLJournalDetails det
left JOIN GLJournal j ..
...
which means you want ALL rows from GLJournalDetails table, regardless of if you have data in GLJournal table for given period or not
I do not know your DB design.
try to remove "left" join
If you can write SQL that will return your correct data for Period 4, I guess it means for April 2009 - April 2010?
I can help you with proc that use parameters
that means you have null in your result so you where filters everything that is null. move your where to "on"
i
But you proc can be much simple, can you describe what define values for you variable @period?
select j.Period, gat.TypeIS, SUM(det.amount) as Amount, ga.AccountID,ga.AccountNam
from GLJournalDetails det
left JOIN GLJournal j ON j.JournalID=det.JournalID and j.Period between (select Period1 from #cuTempPeriod) and (select Period13 from #cuTempPeriod)
left join GLDepts dept on dept.DeptID = det.DeptID
left join GLAccounts ga on ga.AccountID = det.GLID
left join GLAccountTypes gat on gat.AccountTypeID = ga.AccountTypeID and gat.TypeIS in ('Operating Expenses', 'Other Expenses')
group by j.Period,gat.TypeIS, dept.DeptName, dept.DeptID, ga.AccountID,ga.AccountNam
order by j.period, ga.AccountID, dept.DeptName