Stored procedure arguments

[DomTrix]

Hey there, quick question:

Can you set default values for parameters in stored procedures so that if you dont set a parameter when you call the procedure, it gets given the default value?

I assume you can't as I havn't been able to find any info on it...

thanks again

DT

 

[gmmastros]

Something like this...

create procedure DefaultValueTest
	@Name VarChar(100) = 'George'
AS
Select @NAme

Then, when you call it without a parameter, it will return 'George'. If you supply a parameter, it will return the parameter.

Ex.

DefaultValueTest
go
DefaultValueTest 'John'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DomTrix]

Wohoo, nice one. Think I need a course in google search term creation

thanks

 

[TJRTech]

Agh, not for nothing, but you say you couldn't find any information on this, then you conclude that a Google search would have helped.

I ask, did you check SQL Server Books Online and the T-SQL reference?

The following is an excerpt from the CREATE PROCEDURE documentation:




Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Arguments
...

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

...



My advise, always look at Books Online first, and learn to read the Syntax "syntax".

TJR