Hi all, I'm having a play in SSMS to expand my stored procedure writing skills and I've hit an obstacle that I'm not sure how to overcome. I'm writing a simple report that I want to build up to something more complex:
select customer_no, sum(cont_amt) from t_contribution
where fund_no in (1,2,3,4,5) and appeal_no in (1,2,3,4,5)
group by customer_no
That works fine.
Next I want to set up fund_no and appeal_no as variable parameters for my report, however as far as I understand it I am not able to use IN to set the variables. The reports I've written so far have only needed one value in each parameter so I have been able to use =, but that's not going to work in this instance because I need to be able to filter on multiple values. I understand from Mr Google that it's not possible to use IN in this context and that there are workarounds to get the same result, but it's a little bit complicated for me to comprehend and apply back to my specific case here.
Is anybody able to please explain how I need to change this stored procedure to allow multiple values within the fund and appeal variables?
This is what I've got at the moment using '=' which will only work on a single fund and appeal:
ALTER PROCEDURE [dbo].[LRP_PHILANTH TEST REPORT_DEMO](
@fund_str varchar(4000),
@appeal_str varchar(4000)
)
AS
Set NoCount On -- added auto CWR 8/9/2001
where fund_no = @fund_str and appeal_no = @appeal_str
;
Many thanks for reading.
George, try
select customer_no, sum(cont_amt)
from t_contribution
where (ISNULL(@fund_str, '') = '' OR CHARINDEX(',' + CONVERT(VARCHAR, fund_no) + ',' , ',' + @fund_str + ',') > 0)
and (ISNULL(@appeal_str, '') = '' OR CHARINDEX(',' + CONVERT(VARCHAR, appeal_no) + ',' , ',' + @appeal_str + ',') > 0)
If @fund_str or @appeal_str are NULL or empty, it will bring all funds or appeals.
Fernando Margueirat Business Analyst The National Ballet of Canada 470 Queens Quay West Toronto, Ontario M5V 3K4 P: 416 345 9686 x453 F: 416 345 8323
http://national.ballet.ca
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of George Browning Sent: Monday, September 21, 2015 11:29 PM To: Fernando Margueirat <FMargueirat@national.ballet.ca> Subject: [Tessitura Technical Forum] Stored procedure help - multiple values within a parameter
selectcustomer_no, sum(cont_amt) fromt_contribution
wherefund_no in (1,2,3,4,5) andappeal_no in (1,2,3,4,5)
wherefund_no = @fund_str andappeal_no = @appeal_str
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Thanks so much for your help Mark and Fernando.
Yes, split list! Untested but should work fine.
declare@fund_str varchar(4000) = null,@appeal_str varchar(4000) = nullselect customer_no, sum(cont_amt) from t_contribution aleft join FT_SPLIT_LIST(@fund_str,',') z on a.fund_no = z.Element left join FT_SPLIT_LIST(@appeal_str,',') zz on a.appeal_no = zz.Element where (@fund_str is null or z.Element is not null)and(@appeal_str is null or zz.Element is not null)group by customer_no