StoredProc Parameter IS NULL and = NULL problem

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

StoredProc Parameter IS NULL and = NULL problem

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-21 : 01:35:32

Hi all,

I am new and learning how to write SP in SQL Server. Currently testing how to write one. I am having some difficulty trying to pass some parameters to the SP to run a simple SELECT.

1. EXEC SP_mySP 123, null
- SELECT * FROM myTable WHERE Col1 = @Param1 AND Col2 IS NULL

2. EXEC SP_mySP null, 123
- SELECT * FROM myTable WHERE Col1 IS NULL AND Col2 = @Param2

3. EXEC SP_mySP 123, 321
- SELECT * FROM myTable WHERE Col1 = @Param1 AND Col2 = @Param2

4. EXEC SP_mySP null, null
- SELECT * FROM myTable WHERE Col1 IS NULL AND Col2 IS NULL

My problem is how do pass the parameter? I can't use "SELECT * FROM myTable WHERE Col1 = @Param1" because "Col1 = NULL" doesn't work. Please advise

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-21 : 01:41:59

yes. for NULL, you have to use IS NULL or IS NOT NULL and not the equal operator


WHERE (@Parmam1 IS NULL or Col1 = @Param1)
and   (@Parmam2 IS NULL or Col2 = @Param2)

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-21 : 02:25:00

I tried your suggestion, somehow it doesn't work correctly. The result returned are:

[myTable]
myID | Col1 | Col2
1 | 111 | null
2 | null | 222
3 | null | null
4 | 111 | 222

1. EXEC SP_mySP 111, null
Expected Result: Row 1
Result: Row 1 & 4

2. EXEC SP_mySP null, 222
Expected Result: Row 2
Result: Row 2 & 4

3. EXEC SP_mySP 111, 222
Expected Result: Row 4
Result: Row 4

4. EXEC SP_mySP null, null
Expected Result: Row 3
Result: Row 1, 2, 3 & 4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-21 : 02:30:45

[code]
where ((@Param1 is null and Col1 is null) or Col1 = @Param1)
and ((@Param2 is null and Col2 is null) or Col2 = @Param2)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-02-21 : 03:15:31

Alright! Thanks, this works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources