|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
CASE statement with an INI am trying to do this, it would simply my sql so much, but the syntax won't parse. DECLARE @Tmp varchar --@Tmp will either be cow or pig after something done here, lets say pig for simplification.. SET @Tmp = 'pig' SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN (1,2) END) To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want to use one select statement but the WHERE clause changes depending on the value of @Tmp. If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this simply not possible or have I got the syntax wrong? it doesnt seem an unlikely thing to want to do... I even tried ditching the idea and doing an if and rewriting the whole select statement depending on @Tmp. Only problem is it is the select statement for a cursor FOR statement, so fiddling around with that is tricky too !!! Hi Louise
Try something like SELECT * From Blah WHERE ( SomeField = 1 AND @tmp = 'cow') OR ( SomeField = 2 AND @tmp = 'pig') John Show quote "louise raisbeck" wrote: > hi there, > > I am trying to do this, it would simply my sql so much, but the syntax won't > parse. > > DECLARE @Tmp varchar > --@Tmp will either be cow or pig after something done here, lets say pig for > simplification.. > SET @Tmp = 'pig' > > SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN > (1,2) END) > > To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want > to use one select statement but the WHERE clause changes depending on the > value of @Tmp. > > If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it > is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried > lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this > simply not possible or have I got the syntax wrong? it doesnt seem an > unlikely thing to want to do... > > I even tried ditching the idea and doing an if and rewriting the whole > select statement depending on @Tmp. Only problem is it is the select > statement for a cursor FOR statement, so fiddling around with that is tricky > too !!! ok, that works [blush]!! thanks.
Show quote "John Bell" wrote: > Hi Louise > > Try something like > > SELECT * From Blah > WHERE ( SomeField = 1 AND @tmp = 'cow') > OR ( SomeField = 2 AND @tmp = 'pig') > > John > > "louise raisbeck" wrote: > > > hi there, > > > > I am trying to do this, it would simply my sql so much, but the syntax won't > > parse. > > > > DECLARE @Tmp varchar > > --@Tmp will either be cow or pig after something done here, lets say pig for > > simplification.. > > SET @Tmp = 'pig' > > > > SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN > > (1,2) END) > > > > To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want > > to use one select statement but the WHERE clause changes depending on the > > value of @Tmp. > > > > If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it > > is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried > > lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this > > simply not possible or have I got the syntax wrong? it doesnt seem an > > unlikely thing to want to do... > > > > I even tried ditching the idea and doing an if and rewriting the whole > > select statement depending on @Tmp. Only problem is it is the select > > statement for a cursor FOR statement, so fiddling around with that is tricky > > too !!! It looks like you are going to have to construct your SQL statement in a
string variable and then use EXEC() to submit the string to the query parser. SET @strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @Tmp + ')' EXEC(@strSQL) HTH, Mike Show quote "louise raisbeck" wrote: > hi there, > > I am trying to do this, it would simply my sql so much, but the syntax won't > parse. > > DECLARE @Tmp varchar > --@Tmp will either be cow or pig after something done here, lets say pig for > simplification.. > SET @Tmp = 'pig' > > SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN > (1,2) END) > > To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want > to use one select statement but the WHERE clause changes depending on the > value of @Tmp. > > If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it > is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried > lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this > simply not possible or have I got the syntax wrong? it doesnt seem an > unlikely thing to want to do... > > I even tried ditching the idea and doing an if and rewriting the whole > select statement depending on @Tmp. Only problem is it is the select > statement for a cursor FOR statement, so fiddling around with that is tricky > too !!! yes that is another solution. thanks. does exec (string) have speed
implications? Show quote "Mike Austin" wrote: > It looks like you are going to have to construct your SQL statement in a > string variable and then use EXEC() to submit the string to the query parser. > > SET @strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @Tmp + ')' > EXEC(@strSQL) > > HTH, > > Mike > > "louise raisbeck" wrote: > > > hi there, > > > > I am trying to do this, it would simply my sql so much, but the syntax won't > > parse. > > > > DECLARE @Tmp varchar > > --@Tmp will either be cow or pig after something done here, lets say pig for > > simplification.. > > SET @Tmp = 'pig' > > > > SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN > > (1,2) END) > > > > To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want > > to use one select statement but the WHERE clause changes depending on the > > value of @Tmp. > > > > If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it > > is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried > > lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this > > simply not possible or have I got the syntax wrong? it doesnt seem an > > unlikely thing to want to do... > > > > I even tried ditching the idea and doing an if and rewriting the whole > > select statement depending on @Tmp. Only problem is it is the select > > statement for a cursor FOR statement, so fiddling around with that is tricky > > too !!! Certainly, this solution is slower than if you're able to come up with a way
to implement dynamic SQL in a fixed statement. Another approach may be: IF @Tmp = 'Pig' BEGIN SELECT... END ELSE BEGIN SELECT... END Show quote "louise raisbeck" wrote: > yes that is another solution. thanks. does exec (string) have speed > implications? > > "Mike Austin" wrote: > > > It looks like you are going to have to construct your SQL statement in a > > string variable and then use EXEC() to submit the string to the query parser. > > > > SET @strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @Tmp + ')' > > EXEC(@strSQL) > > > > HTH, > > > > Mike > > > > "louise raisbeck" wrote: > > > > > hi there, > > > > > > I am trying to do this, it would simply my sql so much, but the syntax won't > > > parse. > > > > > > DECLARE @Tmp varchar > > > --@Tmp will either be cow or pig after something done here, lets say pig for > > > simplification.. > > > SET @Tmp = 'pig' > > > > > > SELECT * From Blah WHERE SomeField IN (CASE @Tmp WHEN 1 THEN (1) WHEN 2 THEN > > > (1,2) END) > > > > > > To explain, i have a value of @Tmp. It will either be 'cow' or 'pig'. I want > > > to use one select statement but the WHERE clause changes depending on the > > > value of @Tmp. > > > > > > If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it > > > is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried > > > lots of ways, locations of @Tmp, brackets etc, and it just wont work. Is this > > > simply not possible or have I got the syntax wrong? it doesnt seem an > > > unlikely thing to want to do... > > > > > > I even tried ditching the idea and doing an if and rewriting the whole > > > select statement depending on @Tmp. Only problem is it is the select > > > statement for a cursor FOR statement, so fiddling around with that is tricky > > > too !!!  |
|||||||||||||||||||||||
Other interesting topics