Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Return All Records When Param Not Passed
Message
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
Return All Records When Param Not Passed
Miscellaneous
Thread ID:
01326506
Message ID:
01326506
Views:
56
I have this procedure, and I want to return all records when no parameter is passed. How do I change the WHERE clause to handle this?
CREATE PROCEDURE spRptPendingShipments
@sTransTypeCodes VARCHAR(MAX) = ''

AS
-- Pull the data
SELECT	d.sDepository_Desc,
		p.sProduct_CD + ' - ' + p.sProduct_Desc1 AS Product_Info,
		p.sProduct_Desc1,
		tr.dtInsert_DT,
		t.iTrading_Partner_ID,
		tp.sTP_Name1,
		t.iOrder_Hdr_ID,
		t.iTrade_Conf_No,
		t.sTrade_Type_CD,
		t.sPayment_Term_CD,
		tr.sTrans_Settle_Ref,
		tr.sTrans_Type_CD,
		CASE 
			WHEN tr.sTrans_Type_CD ='PR' THEN tr.decTrans_Qty
			WHEN (tr.sTrans_Type_CD = 'PT' AND tr.decTrans_Qty > 0) THEN tr.decTrans_Qty
			ELSE 0
		END AS Receipt,
		CASE 
			WHEN tr.sTrans_Type_CD ='PD' THEN tr.decTrans_Qty
			WHEN (tr.sTrans_Type_CD = 'PT' AND tr.decTrans_Qty < 0) THEN tr.decTrans_Qty
			ELSE 0
		END AS Ship
	FROM Trade t
	JOIN Depository d ON d.sDepository_CD = t.sDepository_CD
	JOIN Product p ON p.sProduct_CD = t.sProduct_CD
	JOIN Transactions tr ON tr.iTrade_ID = t.iTrade_ID
	JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
	WHERE sTrans_Type_CD IN
			(SELECT DataItems FROM udfParseCommaListToTable(@sTransTypeCodes, ','))
	GROUP BY d.sDepository_Desc, p.sProduct_CD, p.sProduct_Desc1, tr.dtInsert_DT, t.iTrading_Partner_ID, 
			 tp.sTP_Name1, t.iOrder_Hdr_ID, t.iTrade_Conf_No, t.sTrade_Type_CD, t.sPayment_Term_CD, 
			 tr.sTrans_Settle_Ref, tr.sTrans_Type_CD,tr.decTrans_Qty
	ORDER BY d.sDepository_Desc, Product_Info, tr.dtInsert_DT, tp.sTP_Name1
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people
Next
Reply
Map
View

Click here to load this message in the networking platform