April 25, 2006 at 1:02 pm
hello everyone,
I am trying to develop a search query where a user has the option of passing search parameters. He has the options of passing 1 or 2 or 3 or 4 or 5 parameters. The store procedure I have developed is as follows
CREATE Procedure PolicyProceduresSearch
(
@SearchTitle nvarchar(255),
@SearchPolicyNumber nvarchar(255),
@SearchDate nvarchar(255),
@SearchSummary nvarchar(255),
@SearchBody nvarchar(255)
)
AS
SELECT
PolicyProcedureID,
IDNumber,
Title,
DateUpdated,
Author,
ExecutiveSummary,
Body,
SupportingDocuments,
Comment,
PolicyNumber ,
PolicyProcedureType,
PolicyProcedureStatusDescription,
PolicyDepartName,
ReviewerName
FROM
PolicyProcedures
WHERE
Title LIKE @SearchTitle + '%'
OR
DateUpdated LIKE @SearchDate + '%'
OR
Author LIKE @SearchTitle + '%'
OR
ExecutiveSummary LIKE @SearchSummary + '%'
OR
PolicyNumber LIKE @SearchPolicyNumber + '%'
GO
However the store procedure is making me enter 5 parameters otherwise it does not work. The user has 5 text boxes and he has the option of entering 2 or 3 parameters or 1 parameter.
How do I modify the store procedure so that the user can enter 2 or 3 or 1 parameter and get the output.
Please guide.
kapil
April 25, 2006 at 1:08 pm
At minimum, you need to set a default value like this:
@SearchTitle nvarchar(255) = NULL,
April 25, 2006 at 1:39 pm
Use this methodology, and so long as the optionals are null, your good....
SELECT
PolicyProcedureID,
IDNumber,
Title,
DateUpdated,
Author,
ExecutiveSummary,
Body,
SupportingDocuments,
Comment,
PolicyNumber ,
PolicyProcedureType,
PolicyProcedureStatusDescription,
PolicyDepartName,
ReviewerName
FROM
PolicyProcedures
WHERE
Title LIKE ISNULL(@SearchTitle+ '%', Title)
AND DateUpdated LIKE ISNULL(@SearchDate+ '%', DateUpdated)
AND Author LIKE ISNULL(@SearchTitle+ '%', Author)
etc...
This also takes the null concatenates to null to be in effect....but I am sure you can see that.....The calls can also handle combinations as well, but always needs all the params to be passed, even if they are null
April 26, 2006 at 3:12 am
Hi Kapil,
you didn't mention what is this search about and what the result should be. I see you are using OR in your WHERE clause. In an example with books that means, if the user enters both Title and Author, it will not display this particular book, but all books of this author and also all books of other authors that contain the Title search string in title.
Is this what you want? Generally, multiple search conditions are used to narrow down the result, while you are expanding it with every condition the user enters. Also, if you really want to do this, how will you treat the fact that one of the parameters was not entered? I suppose this was a mistake in procedure you posted, and it should be (as Scorpion already posted) "AND" instead of "OR". Could you please confirm that or explain how the search should work?
There is another way how to write the query (it also requires the NULL value to be passed when parameter is not entered):
WHERE
(Title LIKE @SearchTitle+ '%' OR @SearchTitle IS NULL)
AND (Author LIKE @SearchAuthor + '%' OR @SearchAuthor IS NULL)
AND (DateUpdated = @SearchDate OR @SearchDate IS NULL)
......
As you see, the OR condition in parentheses makes sure that if you don't enter anything in one of the parameters (parameter IS NULL), the respective condition does not filter anything out. I have changed the SQL a bit, since I don't think it is wise to search for a date using LIKE. Also, be careful with dates and test the parameter on validity before you pass it to the procedure.
HTH, Vladan
April 26, 2006 at 9:44 am
Check this page out, it directly addresses what you are looking to do. http://www.sommarskog.se/dyn-search.html
This will even allow you the option of passing in say, parameters 1 and 3 or only param 5, etc.
This guy's site is AWESOME for examples of ways to do things, you should check out some of the other things he has up there.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply