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 |
|
Eduard
Starting Member
5 Posts |
 Posted - 2004-08-02 : 12:47:30
|
| Hi,I have a stored procedure that receives some parameters. I want to use this parameters in the Where statement. But if some parameter is null i don't want to use it. Using a dinamic SQL and the EXEC is the only way? I want to avoid the grant select to tables... Is it better create duplicate stored procedures with diferents set of parameters? Exist some type of oracle NVL() function in SQL server in order to do the select without using EXEC function?Cheers,Eduard |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-08-02 : 12:55:15
|
| If I read you right, you want to exclude rows using the where clause on certain column values being null or not null as parameters.This is an example where col1 is the column in the table and @col1 is the value you want that column to be, or it may be null:SELECT *FROM myTableWHERE col1 = CASE WHEN @col1 IS NULL THEN col1 ELSE @col1 ENDRaymond |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-02 : 12:55:28
|
| would this work for you?SELECT ... WHERE (((Col1 = @Col1) or (@Col1 is null)) and ((Col2 = @Col2) or (@Col2 is null)) and (...) and ((ColN = @ColN) or (@ColN is null))) and (other conditions)Go with the flow & have fun! Else fight the flow :) |
|
|
|
Eduard
Starting Member
5 Posts |
Posted - 2004-08-02 : 13:28:09
|
| Thanks a lot guys, |
|
|
|
|
|
|
Easy question... I think