March 30, 2004 at 11:23 am
Hello All,
ALTER PROCEDURE usp_SelectClient
(
@Cln_Num char(4)
)
AS
SET NOCOUNT ON
Select Cln_Num from Clients
WHERE
CASE WHEN @Cln_Num='ALL' THEN Cln_Num
WHEN (Cln_Num = @Cln_Num) THEN @Cln_Num
WHEN @Cln_Num = 999 THEN 999 ELSE 0 END > 1
In the above StoredProc I need to display the following Logic:
(and 'ALL' is Default Parameter coming from Crystal Report front-end, CLient 'ALL' doesn't exists)
If the user selects 'ALL' then show All records/All Client numbers;
if he inputs a particular Client=999 then display only that Client equal to 999;
Or he inputs any other Client Number, so that only that client number will be returned.
Does't the above StoredProc make sense or is there a better way to incorporate my requirement.
TIA
March 30, 2004 at 2:26 pm
Have you tried this with a client number = 1 and using the 'ALL' parameter?
I am also confused.. what is the difference between 'ALL' and 999 supposed to be? Can there be client number > 999. Overall this CASE approach is great.
Francis
March 30, 2004 at 3:38 pm
Thanks for reply,
All-includes all Clients and 999 is just another client and should be treated a bit differently LATER.
999 is just a Client Number and it could be thousands of clients. So I guess, I am sorry I didn't expain it well, the question is how to distinguish between ALL (which is not a Client number but which includes ALL Client Numbers ) and a single Parameter - single Client Number.
Well, I guess I could have written 2 IF statements: One to Select All and the other to select individual CLient Numbers....
Any other comments
March 30, 2004 at 3:53 pm
I would generally write this as
CASE WHEN @Cln_Num='ALL' THEN 1
WHEN (Cln_Num = @Cln_Num) THEN 1
WHEN @Cln_Num = 999 THEN 999 ELSE 0 END = 1
in case the cln_num actually is 1 but if it works for your data, there' more than one way to skin a cat. Overall the approach is slick.
Francis
March 30, 2004 at 7:05 pm
Personnally the problem with that approach can show up in a poor execution planned being stored and you loose som performance.
I would do this instead.
-- Primary SP entry point can be reached by using SP name or SP name and ;1
CREATE PROCEDURE dbo.usp_SelectClient;1
@Cln_Num char(4) = 'ALL' -- Set default as all so if someone passes without the parameter it won't fail.
AS
SET NOCOUNT ON
IF @Cln_Num IN ('ALL','999')
EXEC dbo.usp_SelectClient;2
ELSE
EXEC dbo.usp_SelectClient;3 @Cln_Num
GO
-- Alter SP code to return all records. Can reach directly with SP name and ;2
CREATE PROCEDURE dbo.usp_SelectClient;2
AS
SET NOCOUNT ON
Select Cln_Num from dbo.Clients
GO
-- Alter SP code to return specific records. Can reach directly with SP name and ;3
CREATE PROCEDURE dbo.usp_SelectClient;2
@Cln_Num char(4) -- Note no Default here as should not be needed unless you decide to call this directly in some app.
AS
SET NOCOUNT ON
Select Cln_Num from dbo.Clients WHERE Cln_Num = @Cln_Num
GO
Note: when ran you will get 2 message for SP;1 about not finding ;2 and ;3 for dependencies. This is not an issue.
The biggest advantage is you now get a seperate execution plan for All records and 1 for specific records from thsi method.
March 31, 2004 at 6:00 am
barsuk,
Haven't used the case statement in the WHERE clause, but you could use an OR statment to accomplish the same thing.
Select Cln_Num from Clients
WHERE ( (lowercase(@Cln_Num)='all') OR (Cln_Num = @Cln_Num) )
In this example, if the user enters all, no records are filtered. If the user enters a client #, then the the WHERE clause will filter only those records equal to the input parameter.
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply