Programming in MS SQL Server 2012

CREATE Stored Procedure



The following example describes CREATE PROCEDURE statement using the minimum required syntax. This example creates a stored procedure that returns all employees information. This procedure does not use any parameters.

-- Example 98 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'dbo.usp_EmployeeList' AND type = 'P')

           DROP PROCEDURE dbo.usp_EmployeeList

GO

 

CREATE PROCEDURE dbo.usp_EmployeeList

AS

SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.DOJ,

   EMP.Salary,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code


Query Output Screen




Note:

To create a stored procedure in the Enterprise Manager

Expand a server group, and then expand a server.

Expand Databases, and then expand the database in which to create the procedure.

Right-click Stored Procedures, and then click New Stored Procedure.

Type the stored procedure information. Press TAB to indent the text of a stored procedure Press CTRL + TAB to exit the text box, or click an appropriate button.

To check the syntax, click Check Syntax.

To set the permissions, click Permissions.

* * * * *


Email Your Comment To AUTHOR