July 17, 2012 at 12:56 pm
dynamic sql for sure would solve this however this is not normally a good option. can you provide a little more detail on what you are trying to accomplish. that would really help any one out there solve the problem
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 17, 2012 at 12:56 pm
Better handled via a IF clause than in WHERE clause.
July 17, 2012 at 12:58 pm
Or at the risk of being wrong again, I think a catch-all query may be what you are looking for.
July 17, 2012 at 12:59 pm
guerillaunit (7/17/2012)
I am trying to do what the below pseuedo code infers:WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE --no where clause END
What is the correct structure for this?
I would do
IF @test-2 <> ''
BEGIN
SELECT something
FROM dsjkdjsk
WHERE Agent = @test-2
END
ELSE
BEGIN
SELECT
FROM
END
Jared
CE - Microsoft
July 17, 2012 at 1:00 pm
SQLKnowItAll (7/17/2012)
guerillaunit (7/17/2012)
I am trying to do what the below pseuedo code infers:WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE --no where clause END
What is the correct structure for this?
I would do
IF @test-2 <> ''BEGIN
SELECT something
FROM dsjkdjsk
WHERE Agent = @test-2
END
ELSE
BEGIN
SELECT
FROM
END
Hmm... What about this:
WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE 1=1 END
Jared
CE - Microsoft
July 17, 2012 at 1:01 pm
SQLKnowItAll (7/17/2012)
SQLKnowItAll (7/17/2012)
guerillaunit (7/17/2012)
I am trying to do what the below pseuedo code infers:WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE --no where clause END
What is the correct structure for this?
I would do
IF @test-2 <> ''BEGIN
SELECT something
FROM dsjkdjsk
WHERE Agent = @test-2
END
ELSE
BEGIN
SELECT
FROM
END
Hmm... What about this:
WHERE CASE WHEN @test-2 <> '' THEN Agent = @test-2 ELSE 1=1 END
Wont work ; Syntactically wrong.
July 17, 2012 at 2:04 pm
A proper stored proc with an if-else block is the better way - this way each query can be optimized separately, and since pulling the whole table is way different from searching for rows, that's what you want. However, the standard way to do this is...
Select whatever
from table
where @mySearchVar is null OR searchField = @mySearchVar
Since it is an "OR" block, SQL Server can skip evaluation of the second argument if the first is TRUE, so performance on this structure is "OK" but not great. I would expect it to be better than the CASE expression though. Test it and let us know what you find out!
July 17, 2012 at 2:11 pm
Jasmine D. Adamson (7/17/2012)
Select whateverfrom table
where @mySearchVar is not null AND searchField = @mySearchVar
Nope, that will not return all rows when @mySearchVar = ''
The previous post by me is the "correct way to accomplish what is being asked for." Are there better ways to do it? Maybe, but I chose to fix the CASE statement that the OP wanted in case it needs to be applied in this way for a reason. I think the best on performance would be to have 2 stored procedures and at the application layer decide which one to call depending on the existence or absence of text in the text box (or whatever). EDIT: Actually, after running this in some different scenarios with 1 simple condition:
WHERE searchColumn = CASE WHEN @filter < > '' THEN @filter ELSE searchColumn END
I have found this to be pretty effective on performance and the execution plan looks pretty good (from the little that I currently understand about them).
Jared
CE - Microsoft
July 17, 2012 at 2:29 pm
I got it backwards, you just needed to wait for my edit. Happens to the best of us, which is proven by the fact that it happened to me 🙂
You must get used to the fact that there's multiple ways to do things in SQL and not always can we tell what is better. I have seen my method a lot more often than I've seen the case statement method.
July 17, 2012 at 2:42 pm
Jasmine D. Adamson (7/17/2012)
I got it backwards, you just needed to wait for my edit. Happens to the best of us, which is proven by the fact that it happened to me 🙂You must get used to the fact that there's multiple ways to do things in SQL and not always can we tell what is better. I have seen my method a lot more often than I've seen the case statement method.
Of course there are multiple ways to do things, but certain ways will be more efficient than others. Just because you see something more often does not make it more efficient. It will depend on the execution plans and I am not certain how the optimizer will evaluate an OR versus the CASE. I will take a look, but certainly your thoughts are simpler to type and probably more practical on thinking.
Note:
Select whatever
from table
where @mySearchVar is null OR searchField = @mySearchVar
Still does not do what the OP is asking.
When @mySearchVar = '' it evaluates to the following:
SELECT whatever
FROM table
WHERE searchfield = ''
Which may return some rows, but certainly will not return all rows in the table. Maybe you were going for this?
Select whatever
from table
where @mySearchVar = '' OR searchField = @mySearchVar
Which is also a possible way. (Sorry I'm being picky, but the OP never mentioned that the value may be NULL)
Jared
CE - Microsoft
July 17, 2012 at 2:47 pm
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply