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

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_EmployeeList

CREATE PROCEDURE dbo.usp_EmployeeList

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.

* * * * *

Your Comment To AUTHOR