Report Server report pass parameter to stored procedure

IF @Exception_Code = 'Sudafed'
BEGIN
	SELECT
			RIGHT(ORDERNO, LEN(ORDERNO) +1 - patindex('%[^0]%', ORDERNO))       AS [OrderNo]  ,
			HEALTHPLANNAME                                                      AS [Batch]    ,
			RXNUM                                                               AS [RxNum]    ,
			DRUGDESC                                                            AS [PrintName],
			ORIGINALQTYREQ                                                      AS [Qty]      ,
			DAYSSUPPLY                                                          AS [Days]     ,
			TOTALREFILLS                                                        AS [Refills]  ,
			STATUS                                                              AS [Status]
		FROM
			ORDERDETAILS WITH (NOLOCK)
		WHERE
			(DRUGDESC LIKE '%pseud%'
				OR VA_PRODID IN('P0165', 'P0167', 'P0296', 'P0330')
			)
			AND STATUS           <> 5
			AND STATUS           <> 9
			AND(TOTALREFILLS      > 5
				OR ORIGINALQTYREQ > 120
				OR DAYSSUPPLY     > 30
			)
		ORDER BY
			DRUGDESC DESC
END -- END IF @Exception_Code = 'Sudafed'

--================================================================================================
-- Sudafed
--================================================================================================
IF @Exception_Code = 'POBox' OR @Exception_Code = 'POBoxDetails'
BEGIN
	DECLARE @POBoxes AS VARCHAR(MAX); SET @POBoxes = '1!2@3#4$5%6^7&8*9(0)1!2@3#4$5%6^7&8*9(0)' -- Just something that should not ever match.
	SET @POBoxes = @POBoxes + ', P%O%BOX, H%C%BOX, PO BX, POB'
	SET @POBoxes = @POBoxes + ', BOX [0-9], BOX[0-9], BOX  [0-9], BOX #[0-9], BOX.[0-9]'
	SET @POBoxes = @POBoxes + ', H C R, HCR[0-9], HCR [0-9], HCR-[0-9]'
	SET @POBoxes = @POBoxes + ', GENERAL DELIVERY, GENERALDELIVERY'
	SET @POBoxes = @POBoxes + ', NO UPS DELIVERY'

	SELECT
			RIGHT(od.ORDERNO, LEN(od.ORDERNO) + 1 - PATINDEX('%[^0]%', od.ORDERNO))      AS OrderNo    ,
			od.STORECODE                                                                 AS 'Site'     ,
			CAST(od.STATUS AS VARCHAR(5)) + ' - ' + s.StatusText                         AS 'Status'   ,
			LTRIM(RTRIM(od.RXNUM))                                                       AS RxNum      ,
			od.PRODUCTDESCRIPTION                                                        AS Product    ,
			od.PATIENTNAME                                                               AS Patient    ,
			oh.PATIENTADDRESS                                                            AS [Address 1],
			oh.PATADDSTREETADD1                                                          AS [Address 2],
			RTRIM(oh.CITY)                                                               AS City       ,
			oh.STATE                                                                     AS State      ,
			oh.ZIPCODE                                                                   AS Zip        ,
			oh.PATIENTPHONE                                                              AS Phone
		INTO
			#PoBoxOrders
		FROM
			ORDERDETAILS AS od WITH (NOLOCK)
			INNER JOIN ORDERHEADER AS oh WITH (NOLOCK) ON od.ORDERNO = oh.ORDERNO
			INNER JOIN dbo.gpd_PopulateTable(@POBoxes) AS pt ON oh.PATIENTADDRESS LIKE '%' + pt.TableData + '%' OR oh.PATADDSTREETADD1 LIKE '%' + pt.TableData + '%'
			INNER JOIN gpd_Status s on s.OrderStatus = od.STATUS
		WHERE
			od.REFRIGERATE = 'Y'
			AND od.STATUS <> 5
			AND od.STATUS <> 9
			AND od.STATUS < 800
			AND NOT (oh.CITY IN ('Green Valley', 'Marana', 'Oro Valley', 'Tucson', 'Vail') AND oh.STATE = 'AZ')
		GROUP BY
			od.ORDERNO            , od.STORECODE   , od.STATUS         , od.RXNUM            ,
			od.PRODUCTDESCRIPTION , od.PATIENTNAME , oh.PATIENTADDRESS , oh.PATADDSTREETADD1 ,
			oh.CITY               , oh.STATE       , oh.ZIPCODE        , oh.PATIENTPHONE     ,
			od.DATETIMEPACKED     , oh.PATNAMELAST , s.StatusText
		ORDER BY
			od.STATUS, od.STORECODE

	IF @Exception_Code = 'POBoxDetails'
	BEGIN
		SELECT 
				* 
			FROM 
				#PoBoxOrders 
			ORDER BY 
				[Status], [Site]
	END
	ELSE
	BEGIN
		SELECT 
			OrderNo, RxNum, Product
			FROM 
				#PoBoxOrders
	END
	
	DROP TABLE #PoBoxOrders
END -- END IF @Exception_Code = 'POBox'

Select all Open in new window