Cacher is the code snippet organizer for pro developers

/******************************
PREPARE TEST PARAMETERS
******************************/

-- declare test variables
DECLARE @cont_st_amt money = null
DECLARE @cont_end_amt money = null
DECLARE @cont_st_date date = null
DECLARE @cont_end_date date = null
DECLARE @campaign VARCHAR(4000) = null
DECLARE @appeal VARCHAR(4000) = null

-- set test values for the variables
--SET @cont_st_amt = null
--SET @cont_end_amt = null
--SET @cont_st_date = null
--SET @cont_end_date = null
SET @campaign = '1025'
--SET @appeal = '3084'


/******************************
BEGIN PROCEDURE
******************************/

-- create table to hold @campaign parameter entry
CREATE TABLE #kcampaign (campaign_no INT, description VARCHAR(100))

-- create table to hold @appeal parameter entry
CREATE TABLE #kappeal (appeal_no INT, description VARCHAR(100))

-- set start and end amounts if not supplied
IF @cont_st_amt IS NULL SET @cont_st_amt = 0.01
IF @cont_end_amt IS NULL SET @cont_end_amt = 9999999.99

-- set start and end dates if not supplied
IF @cont_st_date IS NULL SET @cont_st_date = '1900-01-01'
IF @cont_end_date IS NULL SET @cont_end_date = GETDATE()

/* insert into #kcampaign the values entered for @campaign - if nothing is entered, select all campaign_no values from T_CAMPAIGN

The CHARINDEX line searches for each campaign_no from T_CAMPAIGN and sees if it's in the @campaign value string (ie '240,365,780,890'). 
If the campaign_no is in the string, it'll return a 1, and if not it'll return a 0. The WHERE clause filters out the 0 campaign_nos, 
thereby leaving you just with the campaign_nos that are contained within the parameter string, which are then inserted into #kcampaign.
*/

IF @campaign IS NULL or @campaign = ''
	INSERT INTO #kcampaign
	SELECT campaign_no, description
	FROM T_CAMPAIGN
ELSE
	INSERT INTO #kcampaign
	SELECT campaign_no, description
	FROM T_CAMPAIGN
	WHERE CHARINDEX(',' + CONVERT(VARCHAR,campaign_no) + ',' , ',' + @campaign + ',') > 0

-- insert into #kappeal the values entered for @appeal - if nothing is entered, select all appeal_no values from T_APPEAL
IF @appeal IS NULL OR @appeal = ''
	INSERT INTO #kappeal
	SELECT appeal_no, description
	FROM T_APPEAL
ELSE
	INSERT INTO #kappeal
	SELECT appeal_no, description
	FROM T_APPEAL
	WHERE CHARINDEX(',' + CONVERT(VARCHAR,appeal_no) + ',' , ',' + @appeal + ',') > 0

-- pull the giving and donor information information

SELECT a.customer_no, 
		c.esal1_desc, 
		a.ref_no, 
		a.cont_amt, 
		-- convert datetime to a more readable date format
		CONVERT(varchar(40),a.cont_dt,101) AS contrib_dt,
		d.sort_name
-- INTO temp table #kwork
INTO #kwork
FROM T_CONTRIBUTION a (nolock)
-- JOIN temp table #kcampaign - since this is an inner join, it'll filter the rows based on the @campaign values entered
JOIN #kcampaign b ON a.campaign_no = b.campaign_no
-- JOIN temp table #kappeal - since this is an inner join, it'll filter the rows based on the @appeal values entered
JOIN #kappeal e ON a.appeal_no = e.appeal_no
-- LEFT JOIN the salutation info from TX_CUST_SAL - this won't filter the rows since it's a LEFT JOIN
LEFT JOIN TX_CUST_SAL (nolock) c on a.customer_no = c.customer_no
-- JOIN the customer info from TX_CUST_SAL - this won't filter the rows since it's a LEFT JOIN
LEFT JOIN T_CUSTOMER (nolock) d on a.customer_no = d.customer_no
-- apparently you must filter by TX_CUST_SAL.default_ind = 'Y', otherwise you get mutliple esal1_descs and therefore duplicate rows, find out why
WHERE c.default_ind = 'Y'

/******************************
REVIEW AND CLEAN UP
******************************/

-- check the status of where it's at to this point
--select * from #kappeal
select * from #kwork
ORDER BY sort_name, contrib_dt

-- clean up temporary tables so you can continue testing in this session
DROP TABLE #kappeal
DROP TABLE #kcampaign
DROP TABLE #kwork

GO