Advertisement

Latest Post

Thursday, 1 December 2016

How to Modify a Stored Procedure

How to Modify a Stored Procedure

Same as there is two ways to modify the stored procedure. Following, i am explaining both ways.

 

1:- Using SQL Server Management Studio

To modify a procedure in Management Studio


1.       Connect to an instance of Database Engine and then expand that instance in Object Explorer.
2.       Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
3.       Expand Stored Procedures, right-click the procedure to modify, and then click Modify.
4.       Modify the text of the stored procedure.
5.       To test the syntax, on the Query menu, click Parse.
6.       To save the modifications to the procedure definition, on the Query menu, click Execute.
7.       To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.


2:- Using Transact-SQL

To modify a procedure in Query Editor

1.       Connect to an instance of Database Engine and then expand that instance in Object Explorer.
2.       Expand Databases; expand the database in which the procedure belongs. Or, from the tool bar, select the database from the list of available databases. For this example, select the Payroll database.
3.       On the File menu, click New Query.
4.       Copy and paste the following example into the query editor. 

USE [Payroll]

IF OBJECT_ID ( 'dbo.SP_GetStudents', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.SP_GetStudents;  
GO  
CREATE PROCEDURE dbo.SP_GetStudents
WITH EXECUTE AS CALLER  
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END 

5.       On the File menu, click New Query.
6.       Copy and paste the following example into the query editor

USE [Payroll]

ALTER PROCEDURE [dbo].[SP_GetStudents]
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END
  
7.       To save the modifications to the procedure definition, on the Query menu, click Execute.
8.       To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.
9.       To run the modified stored procedure, execute the following example.

Government Jobs