Optional parameters in stored procedure

[ByzantianX]

I'm using stored procedure for updating table (calling the procedure from vb class module and passing the values). Sometimes I'm passing one and sometimes more than one value. If I use default values for parameters in stored procedure and pass one value I will get the error message. Other words saying, I have to either pass all the values for parameters or none.Is there any solution for that problem?

the code would be like this:

create sp1
@prm1 int=5,@prm2 bit=true
as
update tbl
set fld1=@prm1,@fld2=@prm2
return

 

[Bernadette]

Hi There

You can give parameters default values. If you dont supply a default value when creating the stored procedure it will look for a parameter when executing the stored procedure

Example
-------
Here is a stored procedure that expects 2 parameters @param1 and @Param2. @Param1 defaults to NULL and @Param2 defaults to 1


CREATE PROCEDURE MyStoredProc
@param1 int=NULL -- Defaults to Null
@param2 int=1 -- Defaults to 1
AS
SELECT @param1, @param2
GO


If you call the stored procedure and pass nothing ..
Exec MyStoredProc
the result will be
(null) 1 - All Defaults

If you call the stored procedure and pass @param1
Exec MyStoredProc 10
the result will be
10 1 - @Param2 defaults to 1

If you call the stored procedure and pass @param2
Exec MyStoredProc @param2=50
the result will be
(null) 50 - @Param1 defaults to NULL


Good Luck

Hope This Helps
Bernadette

 

[ByzantianX]

Thank you very much on your suggestion. The problem is that this is perfectly working in query analyzer. I am trying to pass the parameters from vb class module using the "adodb.command" object and I'm constantly getting the error message. I concluded that I have to pass all the parameters (that update non allowed null fields) in order to successfully run the procedure. I tried several ways of passing the values of parameters to the stored procedure but it always ended up like this. Once again, using the default values of parameters in stored procedure works perfectly when it came to sql server's query analyzer but it responds the error message when it came to ado command object. So far I really have no solution for that (except for picking up the old values and passing them as new parameters regardless on whether they are changed on not; but that would really be only an ad hoc solution).