December 7, 2007 at 8:49 am
I have several procedures, that I code like this for passed variables. Is the best way to work with parameters?
Create Procedure dbo.usp_Procedure1
@CallID int = Null
AS
Declare @Call_startint
Declare @Call_endint
IF @CallID is null
begin
Set @Call_start = -2147483648
Set @Call_end = 2147483647
end
ELSE
begin
Set @Call_start = @CallID
Set @Call_end = @CallID
end
SELECT CallID, Call Desc
FROM dbo.CallLog
WHERE CallID Between @Call_start AND @Call_end
December 7, 2007 at 10:21 am
Well, that's a pretty wide open question. In the example you provided, it could be re-written as ...
Create Procedure dbo.usp_Procedure1
@CallID int = Null
AS
SELECT CallID, Call Desc
FROM dbo.CallLog
WHERE CallID Between COALESCE(@CallID, -2147483648) AND COALESCE(@CallID, 2147483647)
and looking closer, it looks like your saying if the parameter is null, then you are effectively illimnating the WHERE clause?
So this would work as well ...
Create Procedure dbo.usp_Procedure1
@CallID int = Null
AS
SELECT CallID, Call Desc
FROM dbo.CallLog
WHERE CallID = ISNULL(@CallID, CallID)
______________________________________________________________________
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. SelburgDecember 7, 2007 at 11:17 am
You don't need the ELSE clause. If the parameter is not null, it's value will be already assigned.
December 7, 2007 at 11:26 am
Thanks, that's a lot cleaner with multiple parameters.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply