Solved

SQL Server 2008 - Using Local Variable in Where Clause of Stored Procedure

Posted on 2011-03-23
9
530 Views
Last Modified: 2012-05-11
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.
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

Open in new window

0
Comment
Question by:toddpotter
  • 6
  • 3
9 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 35198028
You have left join
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.AccountName,ga.AccountNumber, dept.DeptName
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.AccountName, ga.AccountNumber
order by j.period, ga.AccountID, dept.DeptName
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 35198212
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
0
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
ID: 35198367
An one more - you final result only using period1 and period13
which based on you "ifs" is the same as
select @period as period1, @period -(89+11) as period13
you also don't need any temp tables

so you proc will be
----------------------
create PROCEDURE [dbo].[rpt_cuTP_ExpenseAnalysis_test]
 @Period  INTEGER
AS
begin
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 and j.Period between @period and @period -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.AccountName, ga.AccountNumber
order by j.period, ga.AccountID, dept.DeptName
end
----------------------

0
 

Author Comment

by:toddpotter
ID: 35198910
@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?

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 11

Expert Comment

by:Larissa T
ID: 35200709
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
0
 

Author Comment

by:toddpotter
ID: 35200815
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

0
 
LVL 11

Expert Comment

by:Larissa T
ID: 35201078
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_ExpenseAnalysis_test]
 @Period  INTEGER
AS
begin
declare @fromPeriod integer, @toPeriod integer
select @toPeriod = datepart(year,getdate())+@Period
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.AccountName,ga.AccountNumber, dept.DeptName
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.AccountName, ga.AccountNumber
order by j.period, ga.AccountID, dept.DeptName
end
----------------------
0
 

Author Comment

by:toddpotter
ID: 35201653
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.  

0
 
LVL 11

Expert Comment

by:Larissa T
ID: 35202246
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
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now