Stored procedure Optional parameter default value assignment

I have a parameter for stored procedure, which I want to make optional and assign default value from a table, how do I do this?开发者_JS百科

ALTER PROCEDURE [DBO].[Test]

      @planId int = 1  ( This I can do )

But I would like to do

@planId int = ( Select Id from Table1 Where name = ‘ABC’) ‘ Assign default id from a table

If you want to do this, just assign 0 initially and in the first line of the procedure write this value into that variable.

ALTER PROCEDURE [DBO].[Test]
@planId int = 0
AS
BEGIN
SET @planId = ( Select Id from Table1 Where name = ‘ABC’)
...
...
...
END

Within the stored procedure you can declare and set the value. In this case you can set @planId to whatever you want then in the body:

CREATE PROCEDURE procedureName
    @planId int = null output
AS
    IF @planId = null
      Begin
      SET @planId = ( Select Id from Table1 Where name = ‘ABC’) 
      End
    ELSE --> then they gave you the value