Multiple Variables in stored procedure....help!!!
-
This is starting to make my head hurt. I appreciate any help you can give!
I am creating a sp which will allow the user to essentially search within a table for records they may want to see by multiple criteria. They may choose to enter a value in one, two etc of the variables.
This is the start of the proccedure
CREATE PROCEDURE [Pres].[uspRetrieveAllPres]
@BPID int,
@LN nvarchar(50),
@FN nvarchar(50),
@mn nvarchar(50),
@GID char(1),
@YG int,
@s-2 nvarchar(50),
@sid int,
@DC datetime,
@dm datetime
What I am having trouble with is the select statement that goes with it. I dont know how to say "if @BPID is null, then @LN etc etc" or allow them to enter multiple criteria.
The select statement I have is:
Select BPID, LN, FN, MN, GID, YG, S, SID, DC, DM
from [Pres].[Pres]
WHEREBPID = @BPID
Order By BPID
This only works if you input a BPID. How can I write this so that they may enter any value and leave other values null and not kill my procedure.
Anything that you can do will be greatly appreciated!
Thanks,
Steve
-
Hi,
please read this article, it is great and it will tell lots more than I'm able to post here as direct answer to your question. Hope it helps.. oh, and there are some more articles on the same site that could interest you - look around when you're there! I found answers to many problems on that site.
Dynamic Search Conditions in T-SQL
Just to mention one of methods how to implement such search :
AND (c.City = @city OR @city IS NULL) /*if City not entered, do not filter rows on City; if City was entered, filter on column City*/
But don't rely on it, read the article and choose method that suits your needs!
-
Thanks Vladan,
That article is exactly what I was looking for. I guess my Googling wasnt up to par this early
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply