Can a stored procedure parameter be optional

[CK]

I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.

Regards,
CK

[SQL]

optional parameters have to be at the end of a stored proc
CREATE PROCEDURE get_sales_for_title
@something int,
@title varchar(80) = NULL

AS

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
print 'do something here'
END
ELSE
BEGIN
print 'do something else here'
END
now you can call this proc like this
exec get_sales_for_title 1
or like this
exec get_sales_for_title 1,2
you will see that the print statement will be different for the 2 calls

http://sqlservercode.blogspot.com/

[Tom Moreau]

Try:

create proc MyProc
(
@parm1 int -- mandatory
, @parm2 int = 5 -- optional
)
as
....

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"CK" <c_**********@hotmail.com> wrote in message
news:sB*******************@newssvr29.news.prodigy. net...
I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.

Regards,
CK

[Erland Sommarskog]

CK (c_**********@hotmail.com) writes:

I want the procedure to check for the existence of a paramter and if it is
there, it will process these instructions, otherwise it will process these
instructions. Any ideas? Thanks for your advice.

Yes, consider:

CREATE PROCEDURE some_sp @a int,
@b int = 465 AS
PRINT @a + @b
go
EXEC some_sp 1

Prints 466. You can even say:

EXEC some_sp 1, DEFAULT

to explicitly say that you want the default value to be used.

The most commonly used default value for stored procedure parameters is
probably NULL.

Note that there is no way in the stored procedure to tell whether
the parameter was actually specified in the call, or whether the
default was used. That is, inside some_sp you cannot tell the
difference between

EXEC some_sp 1

and

EXEC some_sp 1, 465
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Yes, as you have seen, but you might want to review your old Software
Engineering notes about coupling and cohesion in code modules.

[Doug]

>es, as you have seen, but you might want to review your old Software
Engineering notes about coupling and cohesion in code modules.

in your world on a cloudless day, what color is the sky?????

[Erland Sommarskog]

SQL (de********@gmail.com) writes:

optional parameters have to be at the end of a stored proc
CREATE PROCEDURE get_sales_for_title
@something int,
@title varchar(80) = NULL

AS

No, there is now such law. While it may be practical to have parameters
with default value at the end, this is perfectly legal:

CREATE PROCEDURE some_sp @x int = NULL, @u INT AS
...
go
EXEC some_sp @u = 123

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQL]

True,
I should have been more specific and should have said that you have to
put them at the end if you want to call the proc with the parameters by
position instead of by name

CREATE PROCEDURE some_sp @x int = NULL, @u INT AS
select getdate()
go
EXEC some_sp @u = 123 --fine

EXEC some_sp 123 --will fail
http://sqlservercode.blogspot.com/