Using IsNull when passed variable value is ''

Question

Post reply

Using IsNull when passed variable value is ''

forum member

Ten Centuries

Points: 1290
More actions
March 30, 2010 at 12:35 am

#386600

Hi
Below is query , I allways have a value for variable1 but for variable2 and variable3 if their value is passed as empty i would like check on IS NULL else if a value exists for them i want do usual comparision how can i do this,please let me know
SELECT @rcount=Count(*)
FROM Table
WHERE
variable1=@variable1
AND variable2=@variable2
AND variable3=@variable3
Thank You

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

vidya_pande

SSCertifiable

Points: 5275

March 30, 2010 at 1:36 am

#1141991

so what is the problem with this query?

BrainDonor SSCoach Points: 19254 More actions · Answer 2

Next time, supply the code required to create some test data - it makes it easier for others, so they'll be more willing to help. It also reduces the chances of misunderstanding.

I think you're after something like this:

declare @variable1 varchar(20), @variable2 varchar(20), @variable3 varchar(20)

CREATE TABLE #Test (variable1 varchar(20), variable2 varchar(20), variable3 varchar(20))

insert into #Test(variable1, variable2, variable3)

VALUES ('Alpha', 'Bravo', 'Charlie'),

('Delta', 'Echo', 'Foxtrot'),

('Golf', 'Hotel', 'india')

SELECT * FROM #Test

SET @variable1 = 'Alpha'

SET @variable2 = 'Bravo'

SET @variable3 = 'Charlie'

SELECT * FROM #Test

WHERE

variable1=@variable1

AND variable2=ISNULL(@variable2,variable2)

AND variable3=ISNULL(@variable3,variable3)

--Ignore @variable2 & 3

SET @variable1 = 'Alpha'

SET @variable2 = NULL

SET @variable3 = NULL

SELECT * FROM #Test

WHERE

variable1=@variable1

AND variable2=ISNULL(@variable2,variable2)

AND variable3=ISNULL(@variable3,variable3)

--Just to prove it is working, give it a combination it shouldn't find.

SET @variable1 = 'Alpha'

SET @variable2 = 'Hotel'

SET @variable3 = NULL

SELECT * FROM #Test

WHERE

variable1=@variable1

AND variable2=ISNULL(@variable2,variable2)

AND variable3=ISNULL(@variable3,variable3)

DROP TABLE #Test

BrainDonor

Steve Hall
Linkedin
Blog Site

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Please provide table structure, sample data, ur input , and YOUR DESIRED OUTPUT.. even if sentences in your post fail to educate us with ur requirement, the desired output part will atleast gives us a head start.

Please go thro the following post to know more:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Now for ur query, i understood it in a different way and i have a piece of code for u

NOTE: I am using the table structure and sample data provided in BrainDonor's post.

Here is a sample query, Please tell us if this is what u were requesting.

DECLARE @variable1 VARCHAR(20), @variable2 VARCHAR(20), @variable3 VARCHAR(20)

DECLARE @Select_Query VARCHAR(MAX) , @And_Statments VARCHAR(1024)

SET @Select_Query = ''

SET @And_Statments = ''

--Ignore @variable2 & 3

SET @variable1 = 'Delta'

SET @variable2 = NULL

SET @variable3 = NULL

SET @Select_Query = @Select_Query + 'SELECT COUNT(*) Row_Count FROM #Test WHERE 1=1'

SET @Select_Query = @Select_Query + ' AND variable1 = '''+@variable1+''''

IF @variable2 IS NOT NULL

BEGIN

SET @And_Statments = ' AND variable2 = '''+@variable2+''''

SET @Select_Query = @Select_Query + @And_Statments

END

SET @And_Statments = ''

IF @variable3 IS NOT NULL

BEGIN

SET @And_Statments = ' AND variable3 = '''+@variable3+''''

SET @Select_Query = @Select_Query + @And_Statments

END

SELECT @Select_Query

EXEC (@Select_Query)

Beedle SSC Enthusiast Points: 150 More actions · Answer 4

Another way to do it (if I'm understanding what your looking for correctly):

SELECT @rcount=Count(*)

FROM Table

WHERE

variable1=@variable1

AND Case when isnull(@variable2,'')='' then 1

Else Case when @variable2=variable2 then 1

Else 0

End

End = 1

AND Case when isnull(@variable3,'')='' then 1

Else Case when @variable3=variable3 then 1

Else 0

End

End = 1

sql_avid_fan Valued Member Points: 54 More actions · Answer 5

--delete from folders_rel

where exists (select b.id

from folders_rel a with(nolock)

inner join folders_rel b with(nolock) on b.folder_id = a.folder_id

and b.polymorphic_id = a.polymorphic_id

and b.id > a.id

and b.id = folders_rel.id)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

sql_avid_fan (4/12/2010)
--delete from folders_rel where exists (select b.id from folders_rel a with(nolock) inner join folders_rel b with(nolock) on b.folder_id = a.folder_id and b.polymorphic_id = a.polymorphic_id and b.id > a.id and b.id = folders_rel.id)

Heh... I just went through this on another thread. Be real careful with the code above. It consitutes a "Triangular Join" and produces a whole lot of reads even in the face of proper indexes. See the following article for why it can be so very bad (sometimes literally millions of times worse than a cursor)...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)