I knew that! :)



>>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?
>>
>
>Kevin,
>
>Include check for your parameter in the WHERE clause like
>
>

>WHERE myWhere OR @TransTypeCodes=''
>

>
>(or IS NULL if you're passing NULL as default).
>
>>

>>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
>>
>>