Avatar of Kaporch
Kaporch
 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

DatabasesMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Kaporch

8/22/2022 - Mon
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


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.