CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005
z-boss
z-boss

Reputation: 17618

Can a not optional parameter follow an optional one in the t-sql stored procedure?

Here's the declaration I'd like to have.

CREATE PROCEDURE UpdateTimeProc  
(  
    @Hours int,  
    @Minutes int,  
    @Seconds int = 0,  
    @ResetCounter bit  
)

Upvotes: 0

Views: 196

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

Yes, there is no requirement that parameters with default values must come before or after parameters without. But this means you need to name your parameters if you're not including all of them. You cannot say...

EXEC dbo.UpdateTimeProc 5, 5, 1;

...and expect 1 to go with @ResetCounter. You'll need to name the parameters (as Conrad's answer shows) if you want to leave the optional parameter out.

Let me repeat: the above syntax will NOT work.

You can optionally do it this way (you only have to start naming parameters after you've named the first one):

EXEC dbo.UpdateTimeProc 5, 5, @ResetCounter = 1;

But I really don't recommend it, since people will have to look up what the other parameters are. Generally we should prefer self-documenting code over terse code.

Upvotes: 6

Conrad Frix
Conrad Frix

Reputation: 52675

Yes but then you have to use named arguments when calling it

For example calling from SSMS would be like so

EXECUTE UpdateTimeProc @Hours = 1, @Minutes =2 , @ResetCounter = 0;

If you were using calling it from another client (lets say C#) you'd need to either not set the value property on the parameter or not include it in the Parameter Collection

Upvotes: 3

Related Questions