?
Solved

help with multi-valued integer paramater using a stored procedure

Posted on 2011-05-05
1
Medium Priority
?
370 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:elucero
1 Comment
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35701885
@fin_nbr and @local_fin_nbr as both declared as int. So these can never hold more than one number. Try to declare them as nvarchar(MAX) also and see if that helps.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question