0

I have one SSRS report which imports data from direct query.(I wrote similar to that down)

select a.[Ultimate Parent Account],b.[Account Internal ID],b.[Parent Account ],count(*) as Opportunities
from T_OPTYLINEVIEW_REP a
inner join T_OPTYVIEW_REP b on a.[Opportunity ID]=b.[Opportunity Internal ID]
where a.[Ultimate Parent Account] in (@Ultimate_Acct_ID)
group by a.[Ultimate Parent Account],b.[Account Internal ID],b.[Parent Account ]

Now if I want this to be stored procedure I did->

create procedure testing_proc(@ultimateaccount varchar(100))
as
select a.[Ultimate Parent Account],b.[Account Internal ID],b.[Parent Account ],count(*) as Opportunities
from T_OPTYLINEVIEW_REP a
inner join T_OPTYVIEW_REP b on a.[Opportunity ID]=b.[Opportunity Internal ID]
where a.[Ultimate Parent Account] IN (select * from STRING_SPLIT(@ultimateaccount, ','))
group by a.[Ultimate Parent Account],b.[Account Internal ID],b.[Parent Account ]

When I put this proc in SSRS it was acting weird like it was pulling few rows only when I selected 'all' in the parameter. Like it was pulling only 4 to 5 rows sometimes only 3 rows and all the time the rows were different.

But when I use the direct query and keep the parameter to select 'all' it pulls all the data which is around 9000 rows.

So why my proc is not working fine? I have used the STRING_SPLIT too if the problem is of commas in multiple selection.

Akina
  • 39,301
  • 5
  • 14
  • 25
karaode
  • 9
  • 4
  • MySQL <> SQL Server - please correct your tags. – Dale K May 25 '21 at 05:33
  • SSRS and syntax claims that the question is SQL Server-related. The tag [mysql] removed. – Akina May 25 '21 at 05:42
  • When you use a multi-value parameter in SSRS it doesn't translate directly to a parameter you would use in SQL Server. – Dale K May 25 '21 at 05:53
  • Does this answer your question? [SSRS multi-value parameter using a stored procedure](https://stackoverflow.com/questions/1256925/ssrs-multi-value-parameter-using-a-stored-procedure) – Dale K May 25 '21 at 05:54
  • no it didn't @DaleK I created the parameter using join(parameter....) My proc is same only to the given answer so no need to make any change in that but now also only 4 to 5 rows are getting fetched – karaode May 25 '21 at 06:22
  • Using multivalued parameters with direct queries vs stored procedures is quite different. You just have to debug them individually. Although in fact, I think I spotted your error `varchar(100)` is not long enough to contain many ids... you need `varchar(max)`. – Dale K May 25 '21 at 06:52
  • yaa thanks a lot @DaleK the issue was with the varchar(100) only now i am getting more rows Again thanks a lot for this but still i am getting 8777 rows from stored proc and 9394 from direct query . For some of the ultimate acct ids like this 'Taylor Communications, Inc.' I am not getting any data in stored procedure but the data is present for direct query. You have helped a lot It would be so great of you if you can help with this thing too – karaode May 25 '21 at 09:34
  • If you have a comma in your data and then you split on a comma its going to cause you issues. You should be using a PK id selecting records. – Dale K May 25 '21 at 09:36
  • 1
    yaa @DaleK the issue is with the comma only Thanks a lot mate U r doing so good by commenting so quickly and helping people like us. Thanks again!!!! – karaode May 25 '21 at 09:58

0 Answers0