Thursday, 1 December 2016

How to Create a Stored Procedure

How to Create a Stored Procedure

You can create stored procedures via two ways. I have explained both ways in below:

1:- Using SQL Server Management Studio

To create a procedure in Object Explorer

  1. Connect to an instance of Database Engine and then expand that instance in Object Explorer. 
  1. Expand Databases, expand the database in which you want to create procedure, and then expand Programmability. 
  1. Right-click Stored Procedures and then click New Stored Procedure. 
  1. On the Query menu, click Specify Values for Template Parameters. 
  1. In the Specify Values for Template Parameters dialog box, enter the Author name, Create Date,Description,Procedure_name,@Param1 etc.  values for the parameters 
  1. Click OK.
  2. Replace the SELECT statement with the following statement in the Query Editor,:
SELECT Id, Name, Age, Address, Department, Gender
FROM [SQLCLR].[dbo].[Employee]

  1. F5 to Execute or click Execute. The procedure is created as an object in the database.
  2. To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.
  3. To run the procedure, Open Object Explorer, right-click the stored procedure that you created with assigned name and select Execute Stored Procedure.

2:- Using Transact-SQL


1.       In Object Explorer, connect to an instance of Database Engine.
2.       From the File menu, click New Query.
3.       Copy and paste the following example into the query window and click Execute. This example creates the same stored procedure as above using a different procedure name.

USE Payroll;  
Create PROCEDURE [dbo].[SP_GetStudents]
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
4.       To run the procedure, copy and paste the following example into a new query window and click Execute. Notice that different methods of specifying the parameter values are shown.

EXEC [dbo].[SP_GetStudents]

5.       Display result.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.