SELECT CASE When @CurveId <> 'selectall'

• JMI

  SSChampion

  Points: 11831

  More actions

  January 29, 2010 at 11:48 am

  #218700

  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.

• Tim Januario-145496

  Ten Centuries

  Points: 1391

  More actions

  February 13, 2010 at 8:48 am

  #1118438

  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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  February 13, 2010 at 10:48 am

  #1118469

  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.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]