Multi-valued parameter parsing for sp's?

Multi-valued parameter parsing for sp's?

• Benet Devereux

  You need to execute your SQL statement dynamically in the stored proc using sp_executesql. You need to concatenate the returned parameters properly to your sql statement. This is how I did it.
  
  You can also try to user the dynamic sql in rs. Its somewhat the same as executing it dynamically on the stored proc. Here's a sample:
  
  ="SELECT     Profile_Operating_Group, Profile_Operating_Unit, Profile_Level, Profile_Wrkfrc, Profile_Capability, SUM(1) AS Count " &
  "FROM   RBT_IndividualStatus " &
  "WHERE     (Profile_Metro_City in (" &  "'" + JOIN(Parameters!MetroCity.Value,"','") + "'" &"))" &
  "GROUP BY Profile_Operating_Group, Profile_Operating_Unit, Profile_Level, Profile_Wrkfrc, Profile_Capability"

  
  
  

• DotNetGuy_03

  [quote OR (@MyCommaDelimVariable  IN (SELECT Value FROM fn_Utilsplit(@MyCommaDelimVariable, ','))))
  GO
  
  
  This works great.
  Thank you so much.
  There are just 2 details, I guess that the variable should be replaced by a column and the Value should be between [].
  
  Should anybody really needs to use dynamic SQL someday, then no need to change the value list, just use this function, it will rebuild the string with ' and should SQL pass the single quotes some day, then it will keep working
  
  CREATE FUNCTION dbo.fn_MultiParamSplitList
  (
  @Text varchar(255)
  )
  RETURNS varchar(255)
  AS
  BEGIN
  DECLARE @Result varchar(255)
  SET @Result =
  CASE when left(@Text,1) <> char(39) then
  char(39) + replace(@Text, ',', char(39) + ', ' + char(39) ) + char(39)
  ELSE
  @Text
  END
  RETURN @Result
  END
  
  then call it like that
  
  CREATE PROCEDURE [dbo].[test_multivalue]
  @pti2 nvarchar(255)
  AS
  BEGIN
  SET NOCOUNT ON;

  declare @SQLString as nvarchar(255)
  set @pti2 = ONGlobals.dbo.fn_MultiParamSplitList(@pti2)
  set @SQLString = 'select pti2 as pti2list from pti2_matrix where pti2 in ( ' + @pti2 + ')'
  EXEC sp_executesql @SQLString
  END
  GO
  
  Regards,
  Philippe

  
  

• hellomoin

  When did privew the report in visual studio 2005 by using like the above stored procedure, I got an error;

  Must declare the scalar variable "@multi_para"

  In case passing only one parameter , it worked and no error.

  but passing multi-value parameters, it caused an error like above

  How to do solve it

  Thanks

  
  

• sagan69

  It is an nvarchar(4000) may be you need to switch to nvarchar(max)

  Philippe

  
  

• MarnixG

  Dynamic SQL doesn't get optimized properly.
  
  I would recommend using a table UDF to return your results properly.
  
  CREATE FUNCTION dbo.fn_UtilSplit
  (
  @Text ntext,
  @Delimiter varchar(20) = ' '
  )
  RETURNS @tResult
  TABLE (
  [Index] smallint NOT NULL,
  Value nvarchar(4000)/* NOT NULL,*/
  
  PRIMARY KEY
  (
  [Index]
  )
  )
  AS
  BEGIN
  DECLARE @bContinue bit,
  @chValue nvarchar(4000),
  @iIndex smallint,
  @iLenDelimiter smallint,
  @iLenText smallint,
  @iEndPos smallint,
  @iStartPos smallint
  
  SET @iLenText = DATALENGTH(@Text)
  SET @iLenDelimiter = DATALENGTH(@Delimiter)
  
  IF (@iLenDelimiter = 0)
  BEGIN
  SET @iIndex = 0
  SET @iStartPos = 1
  
  WHILE (@iStartPos <= @iLenText)
  BEGIN
  SET @chValue = SUBSTRING(@Text, @iStartPos, 1)
  
  INSERT INTO @tResult
  (
  [Index],
  Value
  )
  VALUES
  (
  @iIndex,
  @chValue
  )
  
  SET @iIndex = @iIndex + 1
  SET @iStartPos = @iStartPos + 1
  END
  END
  ELSE
  BEGIN
  SET @bContinue = 1
  SET @iIndex = 0
  SET @iStartPos = 1
  
  WHILE (@bContinue = 1)
  BEGIN
  SET @iEndPos = CHARINDEX(@Delimiter, @Text, @iStartPos)
  
  IF (@iEndPos > 0)
  BEGIN
  SET @chValue = SUBSTRING(@Text, @iStartPos, (@iEndPos - @iStartPos))
  SET @iStartPos = @iEndPos + @iLenDelimiter
  END
  ELSE
  BEGIN
  SET @chValue = SUBSTRING(@Text, @iStartPos, @iLenText)
  SET @bContinue = 0
  END
  
  INSERT INTO @tResult
  (
  [Index],
  Value
  )
  VALUES
  (
  @iIndex,
  @chValue
  )
  
  SET @iIndex = @iIndex + 1
  END
  END
  
  RETURN
  END
  
  
  -------------------------------------------------------------------------------------
  
  CREATE         procedure procReportwithMultipleParamsSample (@MyCommaDelimVariable varchar(1000) = null)
  
  AS
  
  if (@MyCommaDelimVariable = '')
    Set @MyCommaDelimVariable = NULL
  
  SELECT *
  FROM MyTable
  WHERE
   ( (@MyCommaDelimVariable IS  NULL)  OR (@MyCommaDelimVariable  IN (SELECT Value FROM fn_Utilsplit(@MyCommaDelimVariable, ','))))
  GO
  
  
  
  
  

• tooparam

  Hi,

  Here is a quick way to get around this limitation.

  1)

  build the parameter value list like that

  select char(39) +   pti2 + char(39)   as pti2  from pti2_matrix

  problem, the single quotes gets doubled so do this in the sp

  2)

  set @pti2 = replace(@pti2, char(39) +char(39), char(39))

  Then you must use dynamic SQL like that

  3)

  exec ('SELECT pti2 as pti2list from pti2_matrix where pti2 in(' + @pti2 + ')')

   

  Regards,

  Philippe

  
  

• victor_CooK

  It's working now.

  I switched to the charlist_to_table function and it returns all rows.

  
  

• Ernst Kolvenbag

  I've been using the UtilSplit function for a while now but today I noticed something strange.

  When I pass a really, really long parameter list, I only get 800 rows from this function-- but I should be getting 868 in this case.

  Anyone know what might be the cause

  Thanks!

  
  
• Multi-valued parameter parsing for sp's?