Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Parameters

Parameters can be passed to stored procedures by name or by position. An example of each method is presented in Listing 1-23:

Listing 1-23. You can pass procedure parameters by position or by name.

EXEC dbo.sp_who 'sa' EXEC dbo.sp_who @loginame='sa'

Obviously, the advantage of referencing parameters by name is that you can specify them out of order.

You can force a parameter for which a default value has been defined to use that default by omitting it altogether or by passing it the DEFAULT keyword, as in Listing 1-24:

Listing 1-24. Passing DEFAULT for a parameter causes it to assume its default value.

EXEC dbo.sp_who @loginame=DEFAULT

You can specify NULL to supply individual parameters with NULL values. This is sometimes handy ...