Feb 10, 2012

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 

9 comments:

  1. Anonymous2/21/2013

    thanks a lot

    ReplyDelete
  2. Anonymous7/02/2013

    Nice .. Its Helped me lot

    ReplyDelete
  3. nice explanation .........thank uuuuuuu

    ReplyDelete
  4. Anonymous10/31/2013

    nice information and thank a lot...

    ReplyDelete
  5. Anonymous12/01/2013

    nice and helpful information can any1 help me to create triggers in the same manner...

    ReplyDelete
  6. Thaks a lot for give such type of information...

    ReplyDelete