Problem in fetching multivalue parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Problem in fetching multivalue parameter

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-03-08 : 05:28:38

Hello Experts,

Im facing problem in one of the reports while passing multivalue parameter from
ssrs to SP.

please note that in cases suppose there are single word in multivalue paramtere then its works corretly,in that scenario i have made one function see below:-

ALTER FUNCTION [dbo].[fn_MVParam] 
   (@RepParam nvarchar(4000), @Delim char(1)= ',') 
RETURNS @Values TABLE (Param nvarchar(4000))AS 
  BEGIN 
  DECLARE @chrind INT 
  DECLARE @Piece nvarchar(100) 
  SELECT @chrind = 1  
  WHILE @chrind > 0 
    BEGIN 
      SELECT @chrind = CHARINDEX(@Delim,@RepParam) 
      IF @chrind  > 0 
        SELECT @Piece = LEFT(@RepParam,@chrind - 1) 
      ELSE 
        SELECT @Piece = @RepParam 
      INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR)) 
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind) 
      IF LEN(@RepParam) = 0 BREAK 
    END 
  RETURN 
  END

and did changes in sp as follow :-

where branchname in IN (SELECT Param FROM dbo.fn_MVParam(@branchname,',')))

Above is working correct.

But its not working in below scenarion when customer name is like this in ssrs multivalue parameter selection.

ex:-

arg soft ltd
adani power, GNR
GSP AHD
witco - comp

In SP i have used this code:-

where customername in IN (SELECT Param FROM dbo.fn_MVParam(@customername,',')))

please let me know how is it possible to do it?

im using ssrs2005.

Regards,
abhi

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-03-09 : 05:32:26

I think the issue is comes due to at selection paramtere for customer there are more then 4000 char data, due to that its failing. If i manually select some of the customer then its works correct.

Let me know how to fix such scenario?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources