asked on

Optional Parameters in a SQL Server 2008 Stored Procedure

My stored procedure is below. I'm going to be needing to create several stored procedures with optional parameters. If the parameters have a value, the value is being used in the WHERE clause. If the parameters are NULL, then the field is ignored. In the code below, I have a much smaller select statement for the 1st query because fewer tables are required in the join clause. My question is, in 2008, is there any other way to accomplish the same thing as what I'm doing below, perhaps with less code? Any examples welcome.

ALTER PROCEDURE SelectProducts
	-- Add the parameters for the stored procedure here
	@StartDate Date = Null,
	@Territory Varchar(20) = Null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
	if @StartDate is not null and @Territory is null
	begin
	    Select '%' Union
		Select ProductName from ProductDim pd inner join DateDim dm
		on pd.ProductStartDateDimKey = dm.DateDimKey
		and dm.SQLDateStamp >= @StartDate
	end
	else
	begin
		if @StartDate is not null and @Territory is not null
		begin
		    Select '%' Union
			Select ProductName from ProductDim pd inner join DateDim dm
			on pd.ProductStartDateDimKey = dm.DateDimKey
			inner join ActivityProduct ap on ap.ProductDimKey = pd.ProductDimKey
			inner join SpendFact sf on sf.ActivityDimKey = ap.ActivityDimKey
			inner join GeographyDim gd on gd.GeographyDimKey = sf.GeographyDimKey
			and dm.SQLDateStamp >= @StartDate
			and gd.GeographyDimKey = @Territory
		end
	end
end
GO

Open in new window

udaya kumar laligondla

there is no way to specify an optional parameters other than what you are already doing.

Allamz

Dear Kaporch,

Please Have a look at the following links, and you'll have what you want.

http://tim.mackey.ie/SQLOptionalParametersInStoredProcedureWhereClause.aspx
http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

Best Regards,

Mohamed Allam
Senior Solution Developer

JestersGrind

You might be able to simplify your query to this:

SELECT '%' UNION
SELECT ProductName
FROM ProductDim pd INNER JOIN
DateDim dm ON pd.ProductStartDateDimKey = dm.DateDimKey INNER JOIN
ActivityProduct ap ON ap.ProductDimKey = pd.ProductDimKey LEFT OUTER JOIN
SpendFact sf ON sf.ActivityDimKey = ap.ActivityDimKey LEFT OUTER JOIN
GeographyDim gd ON gd.GeographyDimKey = sf.GeographyDimKey
WHERE (dm.SQLDateStamp >= @StartDate OR @StartDate IS NULL) AND
(gd.GeographyDimKey = @Territory OR @Territory IS NULL)

Greg

Kaporch

ASKER

I think that by using a left outer join for the territory and allowing NULLs, I will also include products in the list which have no territories. There might be records like that in the database, which I don't want included in the result set.

Somebody I work with suggested table value parameters, but the examples I've seen involve inserts in the database, not selects. Any other suggestions?

JestersGrind

The best way to implement optional parameters is to give them a default of NULL (which you did in your example) and then use something like this in the WHERE clause:

WHERE (Col1 = @Col1 OR @Col1 IS NULL)

When the parameter is null, it essentially removes it from the where clause.

In your example above, you actually have different joins depending on the optional parameters. That's something different entirely.

Check out the following link regarding conditional joins:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

Greg

ASKER CERTIFIED SOLUTION

Kaporch

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer