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
ALTER procedure [dbo].[stp_load_test](@fin
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.