Hello:
Is it possible to useCASE expression inAND condition? i.e.
-------------
CREATE PROC spBlah
(
@.id INT,
@.val INT
)
AS
SELECT *
FROM aTable
WHERE tableID = @.id
AND
(
CASE @.val WHEN 1 THEN otherCol = someValue END
CASE @.val WHEN 2 THEN otherCol != someOtherVlaue END
)
--------------AND is a Boolean Operator and is also a JOIN condition operator used by databases like SQL Server for extra JOIN search conditions. I don't think it can be used with a CASE statement. Try the link below for CASE statement code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm|||For your case, you might try to build the query dynamically and store the query into a SQL variable. Then use the exec method to execute the query. I don't think you can do the way you are heading. Build only the variable part and then append to the fixed part. Hope I am not confusing you...
declare @.tmp varchar(300)
if(@.val = 1)
set @.tmp = 'otherCol = someValue'
else
set @.tmp = 'otherCol != someOtherVlaue'
set @.tmp = 'SELECT * FROM aTable WHERE tableID = @.id AND ' + @.tmp
exec(@.tmp)
Thanks
No comments:
Post a Comment