Where clause depending of parameter
-
Can somebody help me to write one query instead of 2 ones.
declare @parm smallint
if @parm is null
SELECT * FROM table1
WHERE col is null
else
SELECT * FROM table1
WHERE col=@parm
something like this one
SELECT * FROM table1
WHERE (ISNULL(col,@parm)=@parm )
But it returns me records with null and with value in the column.
I want to return records or with null, or with values in the column depending of parameter
thanks in advance
-
Hi Lina,
You can use case when statement for your reqirment.
declare @parm smallint
set @parm =10
select * from table1 where id = case @parm when null then null else @parm end
thanks
sahu
-
select * from table1 where id = case @parm when null then null else @parm end
where id = null...
Have you tried that before posting?
What's wrong with these two queries?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hey Lina,
declare @parm smallint
Select * from Table1 Where Isnull(COL,'<NULL>') = Isnull ( @parm ,'<NULL>')
Regards
Maths
-
Can you post an example where this will work?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Your code is fine, Lina - but if you really want to avoid IF/ELSE solution, then try this:
create table #table1(id int identity, col varchar(10))
insert into #table1 values ('yes')
insert into #table1 values ('no')
insert into #table1 values (NULL)
insert into #table1 values (NULL)
DECLARE @parm varchar (10)
SELECT * FROM #table1
WHERE (col IS NULL AND @parm IS NULL) OR (@parm IS NOT NULL AND col = @parm)
SET @parm = 'yes'
SELECT * FROM #table1
WHERE (col IS NULL AND @parm IS NULL) OR (@parm IS NOT NULL AND col = @parm)
Result:
id col
----------- ----------
3 NULL
4 NULL
(2 row(s) affected)
id col
----------- ----------
1 yes
(1 row(s) affected)
-
Vladan's example is probably going to be the best example, if you want to keep everything in one single SQL statement.
In general you want to avoid using functions in WHERE clauses (such as ISNULL) because SQL Server will be unable to use indexes.
Personally though, I would stick with the original query, in which you had IF..ELSE.
Think of it... Say you have a table with 1 million rows in it, in the one query example, above, you are asking SQL Server to select data out of a 1 million row table based on two clauses.
In the two query example, using IF...ELSE..., SQL Server will only have to run the query based on 1 clause. The IF @Param IS NULL is not going to take any time at all to be evaluated.
Using the two queries may seem a bit tedious but it will be better performing as far as SQL Server is concerned.
-
Almost forgot:
You could always set ANSI_NULLS OFF and then just run the one query as follows:
SET ANSI_NULLS OFF
SELECT * FROM table1
WHERE col=@parm
SET ANSI_NULLS ON
With ANSI_NULLS OFF, the equals sign "=" can evaluate null values.
-
Thanks to everybody who tried to help me.
Karl, your solution with ANSI_NULLS is great, but your explanation convinced me to use 2 queries.
Frank, Maths! Your solutions don't work . They don't return me records with null in the column if the parameter is null.
With best regards,
Lina
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply