CREATE PROCEDURE web_ViewQueries (

@costcent varchar(4),
@creditcontroller varchar(50),
@QueryType int

)

AS

SELECT 		invoice_no,
		cust_name,
		credit_contname,
		project_no,
		costcent,
		queryID,
		--SY numbers,
		inv_date,
		amount, 
		totamnt,
		Query.ProjMan,
		Query.ProfitCentreMan,
		Query.ClientContactName,
		Query.ClientContactTel,
		Query.Comments,
		QueryType.Description,
		Structure.[Company Description],
		Structure.[Div Level Description],
		Structure.[Div Level],
		Structure.[Bus Unit Level],
		Structure.[Regional Level]
FROM		Invoices
LEFT JOIN	Query ON Query.ID = Invoices.QueryID
LEFT JOIN	QueryType on QueryType.ID = Query.QueryType
LEFT JOIN	Structure ON Structure.[Cost Centre] = Invoices.costcent
WHERE		InvoiceStatus = 12
AND		Invoices.costcent = @costcent
AND		Invoices.credit_contname = @creditcontroller
AND		QueryType.ID = @QueryType
ORDER BY	Invoices.cust_name

quote:

---

Originally posted by qwertyjjj

I sometimes need to pass in blank arguments to a stored procedure.
For example, in a drop down on a webpage, the user select all.
I need it to then ignore part of the where caluse.
I could use various IF statements in the SQL but I understand this is bad practice:

CREATE PROCEDURE web_ViewQueries (

@costcent varchar(4),
@creditcontroller varchar(50),
@QueryType int

)

AS

SELECT 		invoice_no,
		cust_name,
		credit_contname,
		project_no,
		costcent,
		queryID,
		--SY numbers,
		inv_date,
		amount, 
		totamnt,
		Query.ProjMan,
		Query.ProfitCentreMan,
		Query.ClientContactName,
		Query.ClientContactTel,
		Query.Comments,
		QueryType.Description,
		Structure.[Company Description],
		Structure.[Div Level Description],
		Structure.[Div Level],
		Structure.[Bus Unit Level],
		Structure.[Regional Level]
FROM		Invoices
LEFT JOIN	Query ON Query.ID = Invoices.QueryID
LEFT JOIN	QueryType on QueryType.ID = Query.QueryType
LEFT JOIN	Structure ON Structure.[Cost Centre] = Invoices.costcent
WHERE		InvoiceStatus = 12
AND		(Invoices.costcent = @costcent OR ISNULL(@costcent,'')='')
AND		(Invoices.credit_contname = @creditcontroller OR ISNULL(@creditcontroller ,'')='')

AND		(QueryType.ID = @QueryType OR ISNULL(@QueryType ,'')='')
ORDER BY	Invoices.cust_name

---