June 3, 2008 at 8:02 am
I have a stored procedure that takes an optional parameter. I currently issue one select statement when the optional parameter is present and another when the optional value is not supplied. Both select statements are the same. The difference is that one select statement has a where clause and the other doesn't. There must be a way to combine this into one select statement. I just can't seem to figure it out. Below is an example of what I mean (ps. Ignore the fact that I am doing a Select *. I am only doing the Select * to shorten my example.
Thanks
CREATE PROCEDURE dbo.PrepTickets_GET_ALL(@partialKey varchar(12) = Null)
As
Begin
Set NoCount On
If (@partialKey Is Null)
Begin
Select * from TableName
Return
End
Select * from TableName
Where FirstPartOfKey = @PartialKey
Return
End
June 3, 2008 at 8:08 am
CREATE PROCEDURE dbo.PrepTickets_GET_ALL(@partialKey VARCHAR(12) = NULL)
AS
BEGIN
SET NOCOUNT ON
SELECT
*
FROM
TableName
WHERE
FirstPartOfKey = ISNULL(@PartialKey,FirstPartOfKey)
RETURN
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 3, 2008 at 8:42 am
I have never seen IsNull used in this fashion. Am I correct that:
1. If @PartialKey is Null then the statement below conceptially does:
Where FirstPartOfKey = FirstPartOfKey
which would return all rows
2. If @PartialKey is Not Null then the statement below conceptially does:
Where Where FirstPartOfKey = @PartialKey
Thanks
WHERE
FirstPartOfKey = ISNULL(@PartialKey,FirstPartOfKey)
June 3, 2008 at 8:45 am
That is indeed correct.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 3, 2008 at 9:26 am
Thanks so much for the time. This will be a big help to me.
June 3, 2008 at 1:56 pm
WHERE
FirstPartOfKey = ISNULL(@PartialKey,FirstPartOfKey)
From my experience this approach could cause performance disaster.
In most cases this one is more effective:
WHERE
(@PartialKey IS NULL OR FirstPartOfKey = @PartialKey)
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply