April 28, 2008 at 7:43 am
Dear All,
I want to do conditional check like if (a= '' then NULL or a = -1 then NULL ). I don't want to use like
IF ( @variablename = '' or @variablename = -1 )
BEGIN
select @variablename = NULL
END
What i did was,
SELECT
where ABC = (Case when @abc = '' then NULL
when @abc = -1 then NULL
else @abc
end
)
is it the correct way to achieve ? or any other way to do the same ?
Inputs are welcome !
karthik
April 28, 2008 at 7:59 am
ABC will never equal NULL.
you'd need to rewrite it. One possibility is
SELECT
...
WHERE
(ABC IS NULL AND @abc = '')
OR
(ABC IS NULL AND @abc = -1)
OR
(ABC = @abc)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 28, 2008 at 8:16 am
Karthik
First off, if @abc is varchar, then -1 isn't a valid value, although '-1' is.
Second, in the code snippet you posted, you were only using @abc as a means to select ABC from a table. In that case, there's no reason why Jason's example shouldn't work for you. If you really want to set it to NULL, then I think you will need to use IF.
John
April 28, 2008 at 8:47 am
First off, if @abc is varchar, then -1 isn't a valid value, although '-1' is.
I do agree with you. I made mistake.
User will pass a value for @abc,Say forexample @abc = 'Cream' then the following Statement will be executed.
Before that,
Create table SweatList
(
SNo int,
SweatName varchar(50),
ABC varchar(50) NULL
)
insert into SweatList
select 1,'HALWA',NULL
union all
select 2,'PALGOVA','Sweat'
union all
select 3,'MILK CREAM','Cream'
union all
select 4,'BREAD-JAM',NULL
Way 1:
-------
Create Proc dbo.P1
(
@abc varchar(50)
)
as
Begin
select * from SweatList
where ABC = (Case when @abc = '' then NULL
when @abc = '-1' then NULL
else @abc
end
)
END
Way 2:
------
Create Proc dbo.P1
(
@abc varchar(50)
)
as
Begin
Begin
select @abc = NULL
End
select * from SweatList
where ABC =@ABC
END
Also can you tell me why we haven't use -1 in the conditions?
karthik
April 28, 2008 at 9:00 am
Karthik
Way 1 isn't syntactically correct, so you'd have to go with Way 2. But I think Jason's way is better still.
Also can you tell me why we haven't use -1 in the conditions?
I'm sorry, but I don't know what you mean.
John
April 29, 2008 at 1:12 am
There is a function in T-SQL
NullIf(Expression) try that out. It will make your code shorter.
More on this can be found out at
http://doc.ddart.net/mssql/sql70/nos-nz_2.htm
😉
Regards
Rajesh.
MBA-IT (Software Dev. & Management)
SCIT-2007-2009
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply