February 19, 2003 at 12:10 pm
Some time ago, someone here showed me a very cool way to create a dynamic WHERE clause with having to create a dynamic query. It went like this:
WHERE (
CASE
WHEN @var IS NULL THEN 1
WHEN column=@var THEN 1
ELSE 0
END =1
)
So, here's the problem. I want to create a search where the @var might be null, and if it is, return everything. If it's not, I want to run an 'IN (SELECT)' clause, like this:
CASE
WHEN @var IS NULL THEN 1
WHEN column IN (SELECT col FROM anothertbl) THEN 1
ELSE 0
END =1
However, apparently, I can't. Does anyone have any suggestions as to a better way?
TIA,
Oblio
PS, forgot to mention that this is on SQL7; probably real easy on 2k, huh?
Edited by - end-user on 02/19/2003 12:14:44 PM
February 19, 2003 at 12:59 pm
Try something like this. It seems to be working but you need to test for performance if tables are large. The query is not very optimized (IN clause) but allows you to handle everything in one SELECT.
SELECT *
FROM t1
WHERE c11 IN ( SELECT CASE WHEN @var IS NULL THEN c11 ELSE c21 END FROM t2 )
Michael
February 19, 2003 at 1:08 pm
Supposing a table, Module, with a smallint field: ModuleCode, the following code works fine when var is set = NULL or to a smallint number:
DECLARE @var smallint
SET @var = 15
SELECT * FROM Module
WHERE
CASE
WHEN @var IS NULL THEN 1
WHEN ModuleCode IN (SELECT ModuleCode FROM Module WHERE ModuleCode = @var) THEN 1
ELSE 0
END =1
February 19, 2003 at 3:47 pm
Ok, I think I need to rephrase. This is for a search, and I have a table with an int field that's a foreign key. My search may or maynot contain a list of ints that I need to return records for. If it doesn't, I want all records.
So, I handle the incoming list by slicing it into a temp table (another tip I learned here).
If there is a search criteria, I want to select the items 'IN' that temp table, otherwise all (I assume that means not including a WHERE clause).
So:
SELECT Items FROM Table
WHERE(
IF @list IS NOT NULL
BEGIN
ItemID IN (SELECT ID FROM #Table)
END
)
If only this code worked!
February 20, 2003 at 6:48 am
Is there a reason you can't use an IF BEGIN END block of code? Why not put this into a stored procedure:
CREATE PROCEDURE DoSomething
@list VARCHAR(1000) = NULL
AS
IF @list IS NULL BEGIN
SELECT * FROM Table
END
ELSE
-- Build TempTable here by splitting
-- Delimited @list into Table...
SELECT * FROM Table
INNER JOIN #TempTable
ON Table.FK = #TempTable.PK
END
February 20, 2003 at 7:02 am
Yes, I would if I could. But, in reality the example I'm using is just for brevity. I actually have five temp tables that may or may not get created & searched on. And that would create an ugly set of IF conditions with 25 possible execution blocks.
February 20, 2003 at 7:11 am
OK, understood. I know this is probably the last thing you want to hear, but, is there any way to possibly break the process into smaller chunks that can be more easily managed? It sounds like a search form process you're describing. If it is, maybe you could do a couple searches on this site and others for possible architectures that would serve you well in that regard. Sorry I couldn't be of more help...
February 20, 2003 at 7:31 am
Yes, there probably is. But this is for a client, and they really want to do it the Dumb(tm) way. So, picture a page with five *muli-select* boxes... And/Or in this list, and/or in that list, and/or in another list, etc.
The issue to me isn't that it's unreasonable, I'm just frustrated with the stupidity of the SQL coding engine. It shouldn't be that hard, and yet, I can't figure out a way to do it. The CASE statement allows expressions, but apparently, 'col IN (SELECT)' isn't an expression, but 'col = @var' is.
February 20, 2003 at 7:48 am
In my second post, I got col IN (SELECT) to work fine. You just have to ensure that the variable is of the same basic type as the column you are looking on...
I still don't see, however, how this is an issue that the "SQL coding engine" should resolve...why isn't it possible to much of this work in the client program (the building of temp tables or the switching of certain procedures depending on the selection of criteria, etc...) and let SQL do its part in a series of simplified SELECT statements?
February 20, 2003 at 8:23 am
Mine is slightly different, but doesn't work. If @NULL is not null, then there will be a temp table that I want to search on. I'm getting an 'Invalid object name'. My code is:
CASE
WHEN @var IS NULL THEN 1
WHEN col1 IN (SELECT colx FROM #temp) THEN 1
ELSE 0
END =1
As for the engine, simplicity is good, yes, except if I switched SPs based on input, I'd have to create 25 nearly identitical SPs to handle this. The analyzer has to parse and evaluate the code first anyway, right? I just feel that if it's doing the work, why can't it do a little dynamic evaluation that would make all of this so much easier? Like, if I have a list, like @var='2,12,8,11,26', why can't I do this: WHERE col IN (@var) Seems logical, right? There's all kinds of stuff like that that makes development a nightmare. Does SQL2K handle this better? At least I'll get UDFs...
February 20, 2003 at 9:26 am
I think the invalid object name error would be generated because either a) you have to properly qualify the temp table's name, or b) the temp table is not created correctly.
Can you post the code that builds the temp table?
Also, I hear your point about not dynamically evaluating the list stored in the varchar value. To get something like that to work, you'd have to do an exec sp_executesql so that SQL would know you were trying to translate the @list varchar variable into a list of ints...
February 24, 2003 at 1:39 pm
Here's the code that builds the table, but there's nothing extraordinary about it.
IF (@Discipline IS NOT NULL)
BEGIN
SET NOCOUNT ON
SET @l_List=@Discipline
CREATE TABLE #D_tbl(ListItem VARCHAR(3) NOT NULL)
WHILE (CharIndex(',',@l_List)) > 0
BEGIN
INSERT INTO #D_tbl(ListItem)
SELECT SubString(@l_List,1,CharIndex(',',@l_List)-1)
SET @l_List=SubString(@l_List,CharIndex(',',@l_List)+1,Len(@l_List))
END
INSERT INTO #D_tbl(ListItem)
SELECT SubString(@l_List,1,Len(@l_List))
SET NOCOUNT OFF
END
Therefore, the code that uses it should be:
WHERE (
CASE
WHEN @Discipline IS NULL THEN 1
WHEN People_Disciplines.DisciplineID IN (SELECT ListItem FROM #D_tbl) THEN 1
ELSE 0
END =1
)
Any idea?
February 25, 2003 at 3:22 am
I agree with all of jpipes comments.
Your last post will not work as you have made the table optional on an IF statement. Put the create outside of the IF.
Another alternative using the temp table
WHERE (@Discipline IS NULL OR (@Discipline IS NOT NULL AND People_Disciplines.DisciplineID IN (SELECT ListItem FROM #D_tbl)))
Or one not using the temp table at all
SET @Discipline = ','+@Discipline+','
WHERE CHARINDEX(','+CONVERT(varchar,People_Disciplines.DisciplineID)+',',+@Discipline) <> 0
Far away is close at hand in the images of elsewhere.
Anon.
February 25, 2003 at 6:01 am
OK, I'm going to chime in once more...
Actually, I think end-user's code does generate the temp table correctly (see previous post), and I'm not sure technically why end-user's code is not working, so let's try something else...how about going back to a solution that uses stored procedure groups:
CREATE PROCEDURE MyProc
@arg VARCHAR=NULL
AS
IF @arg IS NOT NULL BEGIN
/* Do all logic to build the temp table(s) */
EXEC MyProc;2
END
ELSE BEGIN
EXEC MyProc;1
END
GO
CREATE PROCEDURE MyProc;1 --
AS
SELECT FieldList FROM MyTable
GO
CREATE PROCEDURE MyProc;2
AS
SELECT FieldList FROM MyTable
INNER JOIN People_Disciplines
ON DisciplineID #D_tbl.ListItem
GO
The advantage of this approach, is that a) you will cache execution plans tailored to each of the conditions, b) you tidy up the code by placing each logical search its own procedure, and c) avoid a fairly complex WHERE expression (that I am almost sure won't use an index...)
This approach has scaled well within our reporting tools. It may work out for your solution.
Just a thought,
Jay
ps. sorry if I made any code typos; I was rushing this morning...
February 25, 2003 at 9:24 am
Right, I was afraid that's what it would come down to. That means I'm going to have to create 30 sub-procedures.
I'm interested in the occurance of the semicolon in your naming convention. Does that have signifigance, or is it just convenient?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply