Hello Readers today we will going to learn how to Create STORED PROCEDURE with Default Parameters. I hope you all have gone through my previous post on STORED PROCEDURES. If not you can read by clicking here.
First let us remind the Syntax of STORED PROCEDURE
Syntax:
CREATE/ALTER Procedure <Procedure-name>
(
Input parameters ,
Output Parameters (If required)
)
AS
BEGIN
-- SQL Statements used in the stored procedure
END
So if you want to give some default value to your input parameters just add equalto (=) and the value you want to give after the input parametes. Check out How ………
CREATE PROCEDURE sp_search
(
@id int = 1 -- default value of ID is 1
)
AS
BEGIN
select * from employee where id = @id
END
So while executing if you dont give value to input parameter, it will not throw an error as it pick the default value from the stored procedure itself.
EXEC sp_search -- no error
Some Points
If the default value is provided to the input parameters it does not mean that while executing Stored Procedure, you cannot give value to input parameters. But it simply means that the default value is only taken when no value is provided to the procedure.
You may interested in reading
If you have any query or question you can ask us. Please do write in the comment box below, your contribution will be appreciated.