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?
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
NULL rather than 0 for the default, and then don't for get to check that it's NULL before setting it.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
