sql server programming: In SQL Server 2000. Can I make one of the stored procedure parameter as
optional. so that if I don't specify the parameter it will not throw error.
thanks
Gish smith

Gish
CREATE PROC my_proc
@par1 INT,
@par2 INT = 0
AS
.........
......


[quoted text, click to view]

One option would be to assign "default values" to stored procedure. See following example (picked
up from BOL)
The following example shows the my_proc procedure with default values for each of the three
parameters @first, @second, and @third, and the values displayed when the stored procedure is
executed with other parameter values:
CREATE PROCEDURE my_proc
@first int = NULL, -- NULL default value
@second int = 2, -- Default value of 2
@third int = 3 -- Default value of 3
AS
SELECT @first, @second, @third
GO

EXECUTE my_proc -- No parameters supplied
GO

Displays:

NULL 2 3

EXECUTE my_proc 10, 20, 30 -- All parameters supplied
GO

Displays:

10 20 30

EXECUTE my_proc @second = 500 -- Only second parameter supplied by name
GO

Displays:

NULL 500 3

EXECUTE my_proc 40, @third = 50 -- Only first and third parameters
GO -- are supplied.

Displays:

40 2 50


--
- Vishal