Stored proc parameters
-
Greetings!
I have a stored proc I am using to power a crystal report. I have inserted the section I need assitance with. There are additional sections where I am calculating hours and such.
--begin Parameters
@CUSTNMBR [varchar] (15),
@ADRSCODE [varchar] (15),
@Contract_Number [varchar] (11)
AS--with RECOMPILE
CREATE TABLE #Hours (
[CUSTNMBR] [varchar] (15),
[ADRSCODE] [varchar] (15),
[Contract_number] [varchar] (11),
[Skill_Level] [varchar] (15),
[JanHours] [numeric] (19,2)DEFAULT 0,
[FebHours] [numeric] (19,2) DEFAULT 0,
[MarHours] [numeric] (19,2) DEFAULT 0,
[AprHours] [numeric] (19,2) DEFAULT 0,
[MayHours] [numeric] (19,2) DEFAULT 0,
[JunHours] [numeric] (19,2) DEFAULT 0,
[JulHours] [numeric] (19,2) DEFAULT 0,
[AugHours] [numeric] (19,2) DEFAULT 0,
[SepHours] [numeric] (19,2) DEFAULT 0,
[OctHours] [numeric] (19,2) DEFAULT 0,
[NovHours] [numeric] (19,2) DEFAULT 0,
[DecHours] [numeric] (19,2) DEFAULT 0
)
--select * from #hours
Insert Into #Hours
Select CUSTNMBR, ADRSCODE, Contract_Number, Skill_Level, 0,0,0,0,0,0,0,0,0,0,0,0
from SV00582
Where
CUSTNMBR = @CUSTNMBR --'76202'
and ADRSCODE = @ADRSCODE --'43556'
and Contract_Number = @Contract_Number --'19049'
group by CUSTNMBR, ADRSCODE, Contract_Number, Skill_Level
The proc works if the Customer, address code and contract number parameters are supplied. However, I would like to allow the user to specify different scenarios:
All Customers, ALL address codes and all contracts
One customer, all addresses and all contracts
One customer, one address and all contracts
Thanks in advance for any assistance.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec -
There are several ways to approach this kind of problem:
1. Create one stored procedure per search type and one stored procecedure which is a wrapper of the above, then, call the wrapper and perform the selection in the wrapper. (this is my prefered method)
2. substitute each parameter comparison using IS NULL to account for a missing parameter, indicating that you want them all ex:
(customer = @customer or @customer IS NULL) and
(adrscode = @adrscode or @adrscode IS NULL) and ...
This second option can be inefficient in occations
Cheers,
* Noel -
I use a CASE within my WHERE to solve this type of problem.
WHERE CUSTNMBR =
CASE WHEN @CUSTNMBR IS NULL
THEN CUSTNMBR
ELSE @CUSTNMBR END
AND ADRSCODE =
CASE WHEN @ADRSCODE IS NULL
THEN ADRSCODE
ELSE @ADRSCODE END
AND Contract_Number =
CASE WHEN @Contract_Number IS NULL
THEN Contract_Number
ELSE @Contract_Number
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply