input parameter question

[cyberbiker]

Is it possible to create an optional input parameter in a stored procedure with a default value?
ie: if no parameter is passed I want the value to be -1.
I feel certain that I can now write a stored procedure that checks for a null value or a zero and then do this or that if the value is null or 0, but really need to know if there is a way similar to declaring a functions argument as optional in VB available in T-SQL
Also, is it possible to pass a parameter to a stored procedure by using the connection.execute method?
My understanding is that it is not possible to pass a parameter using the connection.execute method and that I need to create a command object instead.
However, we have a "brilliant" individual here who insists that I can pass a parameter using the connection.execute method. He has the ear of management who takes what he says as if passed from the mount. I now need to either find a way to pass an optional parameter from a connection object or find a darn good explanation as to why it cannot be done.
Help will be greatly appreciated.

Terry (cyberbiker)

 

[tlbroadbent]

You set the default value for parameters in the following manner.

Create Procedure MyProc
--required, if not supplied SP will error
@param1 varchar(40),
--optional, if not suplied value will be -1
@param2 int=-1
As Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cyberbiker]

Thanks Terry,
I read these postings as often as I can and have learned much from all the experts. Your posts have particularly been of value with advice and helpful links.

I have a closely related problem that I will post in another thread. Terry (cyberbiker)

 

[computergeek]

Hi,

Trying to do the same thing...

CREATE PROC cp_MyProc
(@GasdayParm Datetime=GetDate())
AS
BEGIN

I keep getting a syntax error...is the syntax wrong, or is it that I can't call a function within the Create Statement?

CREATE PROC cp_MyProc
(@GasdayParm Datetime='2003/06/02')
AS
BEGIN

This version works...


Computergeek

 

[tlbroadbent]

You can't use a function call as the default value of a parameter. SQL BOL says, "The default must be a constant or it can be NULL."

You can do the following.

CREATE PROC cp_MyProc
(@GasdayParm Datetime=null)
AS
BEGIN

If @GasdayParm Is Null
Set @GasdayParm=getdate()


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[deborahb]

How about functions? Is it possible to have optional parameters in functions? I've been trying to use the stored procedure syntax (@v_field = null) and it compiles fine but when I run it with less than the total number of parameters I get an error.

Thank you.

 

[ESquared]

You cannot have optional parameters in functions, even though it seems like it should work because it accepts them in the function itself. You simply get an error when you leave a parameter out, as you have found.

-------------------------------------
A sacrifice is harder when no one knows you've made it.