SP wildcard
-
I have a stored procedure with multiple parameters. What I want to do is default the parameters to null if no value is sent to the stored procedure when called, then if a value is null, either do not include it in the WHERE clause or set it to include all records. For example....
procedure1 @var1=null, @var2=null
SELECT * FROM Pubs
WHERE LastName = @var1 AND IsActive = @var2
If I pass a value for both parameters then I'll get back the correct records. What I want it to do if I don't pass one of the parameters is to include all records for that field. i.e. if I didn't pass a value to @var1, then have it do the equivalent of "SELECT * FROM Pubs WHERE LastName LIKE '%' or "SELECT * FROM Pubs WHERE IsActive = @var2"...so it would include all LastNames and wouldn't limit the data returned based on this field. Is there an easy way to do this?
-
Hi Nathan
I've had to solve this before. Try this:
WHERE LastName = IsNull(@var1,LastName) AND
IsActive = IsNull(@var2, IsActive)
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
There are multiple ways of doing this ....
1) Simple Null check in the where clause
2) Dynamic SQL
Since the query in question is not so huge i guess we can go in for the first option.
WHERE ((@var1 IS NULL AND LastName = LastName) OR (LastName = @var1 AND @Var1 IS NOT NULL)) AND ((@var2 IS NULL AND IsActive = IsActive) OR (IsActive = @Var2 AND @Var2 IS NOT NULL))
We use this kinda querying very often since we have multiple conditions that we need to check for before using the variable value in the where clause and this works just fine.
Cheers!
Arvind -
Thanks Arvind and Phil for the replies, unfortunately they won't work. I'll blame it on a bad post on my part. Let me try to rephrase this a little better. If I pass a parameter to the stored procedure, the stored procedure should search on that criteria. If no parameter is passed, then it should not search based on that parameter.
WHERE ((@var1 IS NULL AND LastName = LastName) OR (LastName = @var1 AND @Var1 IS NOT NULL)) AND ((@var2 IS NULL AND IsActive = IsActive) OR (IsActive = @Var2 AND @Var2 IS NOT NULL))
This will only return records where Lastname is NULL or matches the value passed as a parameter.
What I'm looking for is it to return all records if nothing is passed...the equivalent of "SELECT * FROM tablename"
If a parameter is passed, then return the equivalent of "SELECT * FROM tablename WHERE value = <parameter>"
I was wondering if there was any way I could do this with a CASE statement in the WHERE clause?
-
something like this??
use pubs
DECLARE @au_ID Varchar(20)
DECLARE @au_lname Varchar(100)
SET @au_ID = '172-32-1176'
-- SET @au_ID = NULL
SET @au_lname = 'White'
-- SET @au_lname = NULL
SELECT * FROM dbo.authors WHERE
au_id = CASE WHEN @au_id IS NULL THEN au_ID ELSE @au_ID END
AND
au_lname = CASE WHEN @au_lname IS NULL THEN au_lname ELSE @au_lname END
-
I understand what you are saying...and yes you can also use case like how WINASH has shown above...but my query
WHERE ((@var1 IS NULL AND LastName = LastName) OR (LastName = @var1 AND @Var1 IS NOT NULL)) AND ((@var2 IS NULL AND IsActive = IsActive) OR (IsActive = @Var2 AND @Var2 IS NOT NULL))
will also work fine in the situation you describe.
(@var1 IS NULL AND LastName = LastName)
The condition above checks if value of the variable that you send @var1 IS NULL and if so it will return all the rows without checking for that condition by using LastName = LastName.
Select * from Table1
is the same as
Select * from Table1 where col1 = col1
I hope that clarifies the situation. Pls try it out and let me know if that helps.
Cheers!
Arvind -
Arvind! It works! Thank you...I completely misread your first post. It works like a charm...thanks!
-
I was puzzled about what was wrong with Phil's query, so I tried it against the employee table in the pubs database. It seems to work perfectly!
declare
@var1 varchar(30),
@var2 char(1)
set @var1 = null
set @var2 = null
-- set @var1 = 'maria'
-- set @var2 = 'j'
select * from employee
WHERE fname = IsNull(@var1, fname) AND
minit = IsNull(@var2, minit)
Steve
-
Thanks Steve, saves me responding
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply