January 29, 2010 at 11:48 am
This works fine...
declare @CurveId varchar(32)
set @CurveId = 'selectall'
Select * From ZeroCurve where CurveId =
Case When @CurveId <> 'selectall' Then @CurveId
Else CurveId End
and COB = '1/28/2010'
But what if I need to use "...where CurveId IN(@CurveId)..." ?
How in this case my CASE would look like?
This is from SSRS DataSet.
@CurveId is Multi-value parameter.
February 13, 2010 at 8:48 am
You actually can't do what you are trying to do very easily. The problem is that when the variable comes in, it is not a list like you may expect, but a single string value that probably has a delimiter in it (like a comma) which isn't seen as a delimiter by sql. An example: Let's say that the parameter comes in as a list of integers that meet the requirement of your where clause such as : @ids = '1,2,3,4'
You sql is : select * from tbl where id IN (@ids)
This gets evaluated as : select * from tbl where id IN ('1,2,3,4') which is incorrect because the value '1,2,3,4' is a string that cannot be converted into an integer value. There is really no easy way to do this prior to sql server 2008.
The best solution is to use a function to split your comma-separated list into a table and then join to that table.
Here is some code for that function (slightly modified to return a varchar instead of an int for uses on more datatypes that just int) taken from http://www.sommarskog.se/arrays-in-sql-2005.html to do that.
CREATE FUNCTION dbo.Array (@list nvarchar(MAX))
RETURNS @tbl TABLE (val varchar(100) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (val)
VALUES (substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
Using this function you could then do:
select * from tbl where id in (select val from dbo.Array(@ids)) which will do what you need.
Tim Januario
February 13, 2010 at 10:48 am
Tim Januario-145496 (2/13/2010)
You actually can't do what you are trying to do very easily.
You can, and it looks ok, but you wouldn't want to;
DECLARE @CurveId varchar(60)
SET @CurveId = ',' + 'selectall,Option 1,Option 2,Option 3' + ','
SELECT @CurveId AS [Parameter]
DROP TABLE #Sample
CREATE TABLE #Sample (RowID INT, CurveId VARCHAR(10))
INSERT INTO #Sample (RowID, CurveId)
SELECT 1, 'Option 1' UNION ALL
SELECT 2, 'Option 2' UNION ALL
SELECT 3, 'Option 3' UNION ALL
SELECT 4, 'Option 4'
SELECT * FROM #Sample
WHERE @CurveId LIKE CASE WHEN @CurveId = 'selectall' THEN @CurveId ELSE '%,' + CurveId + ',%' END
SET @CurveId = 'selectall'
SELECT @CurveId AS [Parameter]
SELECT * FROM #Sample
WHERE @CurveId LIKE CASE WHEN @CurveId = 'selectall' THEN @CurveId ELSE '%,' + CurveId + ',%' END
Because the performance is likely to be poor - any index on CurveId won't be used.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply