Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-23
9
Medium Priority
?
572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

721 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