SELECT syntax when variable can be NULL 1

[sunaj]

I want to select rows in a stored procedure depending on the value of an input variable. The variable can be null. What is the best way?

-------------------------------------
Declare @sound int
SET @Sound=NULL
--This does NOT work
select * from tblSpot where Sounding=@sound
--This works, but is not very elegant (I have many variables to check)
select * from tblSpot where Sounding=@sound OR (Sounding IS NULL AND @sound IS NULL)
------------------------------------- Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[codestorm]

create procedure proc1 (
@sound int = null
)
as
select * from tblSpot where Sounding=@sound codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[sunaj]

??
That does not work (as I pointed out in the question). Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[codestorm]

it sounds like you want to take a param into a SP, and do a simple select on a table where the param matches values in a field of the table (even if it is to match a null value).

Hence, the above SP should work - note there is a difference between
create procedure proc1 (@sound int = null)
and
declare @sound int = null
.. ahh, just clicked its an int not a char type field .. I wound't imagine ints and NULLs would mix too well - I'd suggest (if the table field absolutely HAS to allow non-values) that the default non-values be 0 or some such, rather than NULL. A proc like this may then do the job:
create procedure proc1 (
@soundstring varchar(10) = null
)
as
declare @sound int
if @soundstring = null
set @sound = 0
else set @sound = cast(@soundstring as int)
select * from tblSpot where Sounding=@sound codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[sunaj]

Thanks for taking time codestorm.
Defining a value for @sound to represent NULL will not do the trick for me as the column sounding can assume any value (which would not be the same as NULL = no value). Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[codestorm]

Hence my suggestion of changing the sounding column (I assume its currently int nullable) to int not nullable default(0). codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[sunaj]

Which will not work because 0 is not the same as NULL (=value not given) and neither will any other value. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

ps. I know that this is a question of normalizing the database - but that is not practical in this case. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[codestorm]

What I was getting at was something like:

update tblSpot set Sounding=0 where Sounding=NULL
go
exec sp_rename 'tblSpot.Sounding', 'Sounding_tmp', 'COLUMN'
go
alter table tblSpot add Sounding int not null default(0)
go
update tblSpot set Sounding=Sounding_tmp
go
alter table tblSpot drop column Sounding_tmp

codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[codestorm]

Don't ya just hate it when you type something which someone has addressed by the time you submit it ;P

I'll hafta continue this at work tomorrow if I can find the time - time for bed. codestorm
Fire bad. Tree pretty. - Buffy
Kludges are like lies.
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[alfredp77]

you can use &quot;SET ANSI_NULLS OFF&quot; option on the beginning of the the stored proc / query to override this null value behaviour, so your query will simply be like the way it is.