What is Stored Procedure

What is stored procedure

• They are logically grouped SQL or Pl/SQL commands that performs some task.
• When stored procedure is create or alter it compiled and saved in database
• Compiled code is execute when stored procedure is call
• Stored procedure contains input and output parameter
• Stored procedure can contain DML and DQL statement.
• Transaction can be handled in stored procedure

Syntax for create procedure

CREATE PROCEDURE [dbo].[CreateNewPost]
-- Add the parameters for the stored procedure here
<Parameter name >, <Data type>,
<Parameter name >, <Data type> {input, output}
AS
BEGIN
<Queries>
Exception
<exception part>
End;

Example of insert record with output parameter

CREATE PROCEDURE [dbo].[InsertData]
@Title nvarchar(400),
@UserId int,
@Output nvarchar(100) output
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Post
(
Title,
UserId,
)
VALUES
(
@Title,
@UserId,
)
set @Output = @@IDENTITY

END