July 21, 2008 at 2:57 pm
I would like to use a query for several searches in my database. Currently, my stored procedure takes two date parameters and returns some documents. I would like to pass a few other keywords to this stored procedure sometimes to narrow down the search between the two dates to certain keywords.
How can I do this?
I want these additional keywords to be optional. If no keyword is entered, the search should only return results based on dates, but if a keyword is passed to it, then it should return results based on the keyword as well. Thank you for your help.
it looks like this:
@startdate
@enddate
--I'd like to have @keyword1, @keyword2,...
as
begin
select
......
from
.....
join
.....
on....
where date>@startdate and date<@enddate -- how do I say here if there is keyword, then search for
--it, if not, only search for dates
July 21, 2008 at 3:27 pm
One approach would be to set the additional variables to a value that would include all rows if they are not supplied. For example:
if @keyword1 is NULL
set @keyword1 = '@'
...where date between @startDate and @endDate an column >= @keyword1
The probability of survival is inversely proportional to the angle of arrival.
July 21, 2008 at 5:34 pm
Optional parameters can be done using the following:
CREATE PROCEDURE [procedure]
@date1 datetime
,@date2 datetime
,@keyword1 varchar(xx) = NULL
,@keywork2 varchar(xx) = NULL
AS
SELECT columns
FROM table
WHERE datecolumn >= @date1
AND datecolumn < @date2
AND (@keyword1 IS NULL OR keycolumn1 = @keyword1)
AND (@keywork2 IS NULL OR keycolumn2 = @keyword2);
GO
Or, you can nest if statements:
IF (@keyword1 IS NOT NULL)
BEGIN;
SELECT columns
FROM tables
WHERE datecolumn >= @date1
AND datecolumn < @date2
AND keycolumn = @keyword1;
END;
IF (@keyword2 IS NOT NULL)
BEGIN;
[select statement for @keyword2]
END;
etc...
The final option is to use dynamic SQL to build the statement. This might be the best option if there are a lot of optional parameters. If you decide to go that direction, please read the article here: www.sommarskog.se/dynamic_sql.html before you implement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply