Posted by Erland Sommarskog on 03/08/06 00:13

CK (c_kettenbach@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: inserted the image in a column----how can i view the image
• Prev in forum: Re: How does one set read-uncommitted on the entire DB?
• Thread view: Re: Can a stored procedure parameter be optional

[Reply to this message]