How to create stored procedure in sql server management studio (SSMS)

In sql server it is very easy to create stored procedure. We can create a stored procedure in sql server management studio (SSMS) in following way:

Creating stored procure using server management studio (SSMS)

Step 1: Expand your database name node in SSMS. Then expand Programmability node as shown in the following screenshot:

Step 2: Right click on Stored Procedures node and click on New stored procedure...

Step 3: It will generate following sql script in new query page:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       <Author,,Name>

-- Create date: <Create Date,,>

-- Description:  <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

    -- Add the parameters for the stored procedure here

    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

Step 4: Now edit above script like stored procedure name, parameters, procedure body etc according to your requirement . For example:

-- =============================================

-- Author:       Exact Help

-- Create date: 02/10/2012

-- Description:  My first stored procedure

-- =============================================

CREATE PROCEDURE My_First_Proc

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT 'Exact Help'

END

GO

Step 5: To create My_First_Proc stored procedure press F5 button of your keyboard or click on Execute button in SSMS.

Step 6: After creating it will be stored inside Programmability node. To re-open or edit it expand programmability node and right click on your stored procedure name and choose modify option.

You will get the script of your stored procedure. In this case it will be script of My_First_Proc. Which will something like this:

USE [Exact]

GO

/****** Object:  StoredProcedure [dbo].[My_First_Proc]    Script Date: 02/10/2012 10:02:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       Exact Help

-- Create date: 02/10/2012

-- Description:  My first stored procedure

-- =============================================

ALTER PROCEDURE [dbo].[My_First_Proc]

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT 'Exact Help'

END