Link to home
Start Free TrialLog in
Avatar of elucero
elucero

asked on

help with multi-valued integer paramater using a stored procedure

I cannot get this to work. I am using ssrs 2008.   I am using the udf list_to_tbl ValentinoV created in is blog and my stored proc is the following.  I created the dataset in ssrs and I can get it to retrive on value (225), but not mulitple values (225,226,229).  My values are like this 225,226,229.

ALTER procedure [dbo].[stp_load_test](@fin_nbr int)
as
begin

declare @local_fin_nbr int
set @local_fin_nbr = @fin_nbr

select distinct customer_nbr_txt, Financial_Company_Nbr from DimCustomer
where Financial_Company_Nbr IN(select * from list_to_tbl(@local_fin_nbr))
end
GO

udf

ALTER FUNCTION [dbo].[list_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int 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 (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END
GO
ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial