April 30, 2009 at 8:48 am
hi.
i am making a stored procedure where i pass 3 parameters
and a column is checked for those parameters
select * from employee where empid not in (@First,@Second,@Third)
now if any of the parameter goes null then the stored procedure does not give any result.
If i pass only @First, @Second and @Third as null then the query should become
select * from employee where empid not in (@First,@Second)
i tried COALESCE, but ... not successful...
plz help
April 30, 2009 at 8:54 am
One thing you can do in a situation like this is, if the variable is null, assign it an impossible value.
For example, if it's supposed to be an integer value greater than 0 (like most identity columns), assign it a negative number. Or, if it's supposed to be someone's name (a varchar field), assign it a number.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 12:18 pm
Instead of trying to change the query you can change the procedure declaration. Something like
create procedure MyProc
(
@First int = -1,
@Second = -1,
@Third = -1
)
as begin
select * from employee where empid not in (@First,@Second,@Third)
end
the assignment in this case will only happen if the parameter is not passed in or a null is passed in. without this the in operator will not work because of the null. That make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2009 at 4:13 am
Hello,
Try this
create procedure MyProc
(
@First int = NULL,
@Second = NULL,
@Third = NULL
)
as begin
select * from employee where empid not in (IsNull(@First,0),IsNull(@Second,0),IsNull(@Third,0))
end
Hope helpful...
May 1, 2009 at 6:15 am
Hi All. Thanks for your help.
I worked out another way to get the results.
CREATE PROCEDURE [dbo].[MyProc]
(
@First=null,
@Second int=null,
@Third int=null,
)
AS
select e.*, e.empfname +
case when e.empmname is null then ' ' else ' ' +.e.empmname + ' ' end +
e.emplname as empname
from employee e
where
( (e.EmpID != @ First) or (@ First is null)) and
( (e.EmpID != @Second) or (@Second is null)) and
( (e.EmpID != @Third) or (@Third is null)) and
order by e.empid
GO
Do you think i could face any problem ahead ?
May 1, 2009 at 6:30 am
Hi Mohit
Please have a look to Gail's blog about optional parameters:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Greets
Flo
January 29, 2010 at 1:31 am
OR
WHERE ColumnA = ISNULL(@Parameter,ColumnA)
mmm....I think?
January 29, 2010 at 5:50 am
Hi conradude
Did you check the execution plan? I get a index scan.
Greets
Flo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply