SQL Server Performance Forum – Threads Archive

SQL Q.

We have a table which looks like this Tab1 Col1 Col2 Col3
A NULL 1
A Q 2
B NULL 3 FYI — Combination of B and Q will never exist. Now my requirement is I want to write and query where if I pass Q for Col2 I need to get following back A Q 2
B NULL 3 But if I pass NULL for Col2 I need to get following back
A NULL 1
B NULL 3 Is there a simple way to do this. Thanks,
JM
www.alpna.org

When you say pass, do you mean in terms of a stored procedure? If so, you could always do this. create proc dbo.example_proc
@col2 char(1)
as set nocount on select Col1, case when @col2 is null then null else Col2, Col3 from tblTable …
Is this what you meant? John

Oh wait. I get it. OK never mind that last post. Working on it. John

Well, I find myself still coming back to a stored procedure. If that is OK, the solution should be painless. For example: declare @a char(1)
set @a = ‘Q’ if (@a is null)
begin
select col1,col2,col3 from tbltemp where col2 is null
end
else
begin
select col1,col2,col3 from tbltemp where col1 = ‘B’ or (col1 = ‘A’ and col2 is not null)
end … John

Thank you for your help but it won’t work for me as I will only know the value for column Col2 www.alpna.org

Now I have this as a stand alone statement. The problem with this is you can’t do = null so if you set @a to null it doesn’t work. Can’t use in (@a) either. declare @a char(1)
set @a = ‘Q’ select col1,col2,col3 from tbltemp where col1 = ‘B’ or col2 = @a John

Perhaps this?
select col1,col2,col3 from tbltemp where col1 = ‘B’ or isNull(col2, ”) = isNull(@a, ”) or this select col1,col2,col3 from tbltemp where ((col1 = ‘B’ or col2 = @a) and @a is not null) or (@a is null and col2 is null) John

Any one else? www.alpna.org

<pre id="code"> DECLARE @table TABLE (Col1 CHAR(1), Col2 CHAR(1), Col3 INT) INSERT @table(Col1, Col2, Col3) SELECT ‘A’,NULL, 1 UNION ALL SELECT ‘A’,’Q’, 2 UNION ALL SELECT ‘B’,NULL, 3 DECLARE @var CHAR(1) SELECT @var = NULL SELECT t.Col1, t.Col2, t.Col3 FROM @table t LEFT OUTER JOIN ( SELECT Col1, Col2, Col3 FROM @table WHERE ISNULL(@var,’NULLSUCKS’) = ISNULL(Col2,’NULLSUCKS’)) dt ON t.Col1 = dt.Col1 WHERE (t.Col1 = dt.Col1 AND ISNULL(t.Col2,’NULLSUCKS’) = ISNULL(dt.Col2,’NULLSUCKS’)) OR dt.Col1 IS NULL SELECT @var = ‘Q' SELECT t.Col1, t.Col2, t.Col3 FROM @table t LEFT OUTER JOIN ( SELECT Col1, Col2, Col3 FROM @table WHERE ISNULL(@var,’NULLSUCKS’) = ISNULL(Col2,’NULLSUCKS’)) dt ON t.Col1 = dt.Col1 WHERE (t.Col1 = dt.Col1 AND ISNULL(t.Col2,’NULLSUCKS’) = ISNULL(dt.Col2,’NULLSUCKS’)) OR dt.Col1 IS NULL </pre id="code"> That’s just messed up. Let’s have a contest now to see who can do this with the least amount of code. I’m going to pre- [sniped] myself. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Hey…..I’m tired. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

SELECT t.Col1, t.Col2, t.Col3
FROM @table t
WHERE (ISNULL(t.Col2,0) = ISNULL(@var,0) OR( t.col2 IS NULL AND t.col1 = ‘B’ )) —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Hm btw, is that a homework question? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

check this: declare @col2 varchar(1)
set @col2=’Q’
if @col2 is null
begin
select * from tab1 where col2 is null
end
else
begin
select * from tab1 where [email protected] or
col1 NOT IN(select col1 from tab1 where [email protected])
end

Following worked. select Col1, Col2, Col3
from Tab1 where Col2 = ‘Q’
or
(Col1 not in (Select Col1 from Tab1 where Col2 = ‘Q’)
and Col2 IS NULL)
www.alpna.org

]]>