August 20, 2002 at 9:25 am
HI all,
I am using SQL 2000 (yay!) and I haven't really come across this before. I have an sp which takes in several optional params. One of them is an integer. What I would like to do is set the default value of the param to pull ALL values if nothing is sent in. (where x like @param)
Does anyone have any tips or tricks to help me accomplish this?
TIA,
Beth
August 20, 2002 at 10:00 am
One way to do it is the following (this works against the Products table in the Northwind database):
CREATE PROC usp_ShowProduct
@ProductID int = 0
AS
SELECT
*
FROM Products
WHERE
CASE WHEN @ProductID <> 0 THEN @ProductID
ELSE ProductID
END = ProductID
GO
If you don't specify a value for @ProductID, it'll default to 0. The CASE statement then just uses ProductID (the column) which of course matches against all columns. The one problem with this method is that it will generate a table/index scan as opposed to an index seek, which means a performance hit.
Another way to do it is something akin to the following:
CREATE PROC usp_ShowProduct
@ProductID int = 0
AS
IF (@ProductID = 0)
BEGIN
SELECT
*
FROM Products
END
ELSE
BEGIN
SELECT
*
FROM Products
WHERE ProductID = @ProductID
END
GO
This will get the index seek and unless the query is a very complex one, you should avoid an execution plan recompile.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 20, 2002 at 10:28 am
Thanks for the quick response. Unfortunately the stored proc is already really long and kind of complex so I think I'll just create a different version for readibility and performance. I was hoping there was just something very basic I was missing but alas, not for me!
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply