![]() ![]() To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement: DROP PROCEDURE sp_name The following shows the partial output: Deleting a stored procedure Now, if you execute the stored procedure again, you will see the changes taking effect: EXEC uspProductList Third, click the Execute button, SQL Server modifies the stored procedure and returns the following output: Commands completed successfully. Second, change the body of the stored procedure by sorting the products by list prices instead of product names: ALTER PROCEDURE uspProductList To modify an existing stored procedure, you use the ALTER PROCEDURE statement.įirst, open the stored procedure to view its contents by right-clicking the stored procedure name and select Modify menu item: The stored procedure returns the following output: Modifying a stored procedure Where sp_name is the name of the stored procedure that you want to execute.įor example, to execute the uspProductList stored procedure, you use the following statement: EXEC uspProductList To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure: EXECUTE sp_name ![]() Sometimes, you need to click the Refresh button to manually update the database objects in the Object Explorer. You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures as shown in the following picture: ![]() It means that the stored procedure has been successfully compiled and saved into the database catalog. If everything is correct, then you will see the following message: Commands completed successfully. To compile this stored procedure, you execute it as a normal SQL statement in SQL Server Management Studio as shown in the following picture: Note that in addition to the CREATE PROCEDURE keywords, you can use the CREATE PROC keywords to make the statement shorter. However, it is a good practice to include them to make the code clear.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |