Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smccreadie
Aged Yak Warrior
505 Posts |
 Posted - 2002-11-11 : 13:43:28
|
I'm attempting to run the following statement except I want the ELSE statement to be IS NOT NULL. I can't seem to get the syntax to work. Basically, this is part of a large stored procedure but I want this section to select the rows where there is a matching @preceptor_id passed in or match any preceptor_id if the @preceptor_id is null.Any ideas how to do this with the right syntax or do I need to restructure? INSERT INTO #alerts_wl (clinician, alert_count) SELECT a.user_id, COUNT(a.alert_id) FROM TBL_PT_ALERTS a INNER JOIN TBL_SECURITY_SHELL b ON a.ssid = b.ss_id WHERE a.dateadded >= @beginning_date and a.dateadded <= @ending_date AND b.preceptor_id = CASE WHEN @preceptor_in IS NOT NULL THEN @preceptor_in ELSE NULL END GROUP BY a.user_id |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-11-11 : 13:49:44
|
| AND b.preceptor_id = coalesce(@preceptor_in,b.preceptor_id ) |
 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-11-11 : 13:58:44
|
| Cool, thanks - what would I do without SQLTeam??? |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-11 : 15:52:55
|
AND b.preceptor_id = ISNULL(@preceptor_in, b.preceptor_id ) I've heard that ISNULL is faster than COALESCE when they can be switched. I'm not totally sure, but it seems to fit. (If I'm wrong, can someone please correct me?)Of course, you could've also replaced the NULL in your case with the column name and it would've worked.----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
|
|
CASE and NOT NULL syntax help