Alter Procedure in SQL Server
Table of Content:
To view the text, of the stored procedure
1. Use system stored procedure sp_helptext 'SPName'
OR
2. Right Click the SP in Object explorer -> Scrip Procedure as -> Create To -> New Query Editor Window
Code: To change the stored procedure, use ALTER PROCEDURE statement:
Alter Procedure spGetEmployeesByGenderAndDepartment @Gender nvarchar(50), @DepartmentId int as Begin Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId order by Name End
To encrypt the text of the SP, use WITH ENCRYPTION option. Once, encrypted, you cannot view the text of the procedure, using sp_helptext system stored procedure. There are ways to obtain the original text, which we will talk about in a later session.
Code:
Alter Procedure spGetEmployeesByGenderAndDepartment @Gender nvarchar(50), @DepartmentId int WITH ENCRYPTION as Begin Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId End
To delete the SP, use DROP PROC 'SPName' or DROP PROCEDURE 'SPName'