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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-13 : 11:28:42
|
Simon writes "Consider a simple stored procedure in SQL Server 7.0 with SP2. The Stored Procedure has just 1 input parameter:
Create Procedure "blah"
@blah As varchar(50)
AS
SELECT * FROM blah WHERE blah = @blah
Everything works fine until the input parameter is Null. Then the SQL syntax needs to be changed:
SELECT * FROM blah WHERE blah IS Null
The Stored Procedure could be written to deal with the null like this:
Create Procedure "blah"
@blah As varchar(50)
AS
IF @blah IS NULL
SELECT * FROM blah WHERE blah IS NULL
ELSE
SELECT * FROM blah WHERE blah = @blah
Now consider the same scenario, only I have 4 input parameters. Together these four parameters form the where clause of my select statement. Any of these parameters have the potential for being null.
Is there any alternative to me having to write a different select statement for each combination of IS NULL and IS NOT NULL parameter values? If I have 4 parameters, that's 16 different select statements. And if I had 10 parameters, it would be 100 statements!
I hope the guru has the answer!!
/Simon" |
|
|
|
|
|
'=' versus 'is' and NULL parameters