Create a New Stored Procedure in Sql Server Step by Step

Hello ever one i come with  my new post for sql server users needs in this post, i have described about how to Create a New Stored Procedure in Sql Server Step by Step, so let's start with introduction of Stored Procedure.

Stored Procedure

Definition

     in sql server Database Management System (DBMS), stored procedure is a precompiled set of Structured Query Language (SQL) statements (or code) that can be shared by a number of Diffrent programs. view More About stored procedure on Microsoft Click to Visit MSDN Site

first you need to open sql server management studio login using user name and password and open the database, Expand the database and select and expnad the "Programmability" folder and select the sub folder "Stored Procedure"

Right click on Stored Procedure  >>  select sub menu "New Stored Procedure"

open the syntax code with stored procedure in the Right pan, here is the default syntax of Stored Procedure.

Syntax

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


Now you can write your own sql query using the syntax and create your own procedure

Example

CREATE PROCEDURE Sp_Emp

AS

BEGIN

SET NOCOUNT ON;

SELECT * FROM dbo.Table_Emp

END

GO

after write successfully all the sql statement with the perfect syntax then compile or execute the stored procedure

- Right click and select Execute
- click to execute button on tool bar

it successfully created message is display in the output window,and see the programmability folder the new procedure is stored in this database folder, see all the list of stored procedure is created in the current database

now execute your stored procedure using the following syntax for  for execute statement.

Syntax

EXECUTE <STORED PROCEDURE NAME>

                             OR

EXEC <STORED PROCEDURE NAME>

this statement write in the "New Query" window and execute it.

Example

EXECUTE Sp_Emp

            OR

EXEC Sp_Emp


i complete this post for now, i hop this post is helpful for under stand about the stored procedure, any query regarding this post or any content please comment me or email me.


Thanks 

Krunal patel
Software Developer 

:-) :-)

My Popular post click on Link to view

• Introduction of MS SQL Server
• Create a Database in MS Sql Server 2008 R2
• Create a View in MS Sql Server 2008 R2
• Create a New Database User In Sql Server 2008 R2
• Create a New Stored Procedure in Sql Server Step by Step
• Create a Database Backup in Sql Server 2008 R2