March 30, 2010 at 12:35 am
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
March 30, 2010 at 1:36 am
so what is the problem with this query?
March 30, 2010 at 1:57 am
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
March 30, 2010 at 3:07 am
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://qa.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)
March 30, 2010 at 8:06 am
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
April 12, 2010 at 12:35 pm
--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)
April 12, 2010 at 1:06 pm
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://qa.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply