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
thanks a lot
ReplyDeleteNice .. Its Helped me lot
ReplyDeletenice explanation .........thank uuuuuuu
ReplyDeletenice information and thank a lot...
ReplyDeletethanku very much
ReplyDeletenice and helpful information can any1 help me to create triggers in the same manner...
ReplyDeletethanks
ReplyDeletethank you
ReplyDeleteThaks a lot for give such type of information...
ReplyDelete