Create, Modify, Delete, Execute Stored Procedure In SQL Server Database

This article will show you how you can create, modify or alter , delete and execute a stored procedure in MS SQL server database.

Please check the Data base

Create Stored Procedure:

Syntax:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>        -- Add the parameters for the stored procedure here        <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,        <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN        -- SET NOCOUNT ON added to prevent extra result sets from        -- interfering with SELECT statements.        SET NOCOUNT ON;     -- Insert statements for procedure here        SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END

Example:

CREATE PROCEDURE SP_ShowTestData AS BEGIN        Select * from TestData; END GO

Alter Stored Procedure:

Syntax:

ALTER PROCEDURE <Procedure_Name, sysname, ProcedureName>        -- Add the parameters for the stored procedure here        <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,        <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN        -- SET NOCOUNT ON added to prevent extra result sets from        -- interfering with SELECT statements.        SET NOCOUNT ON;     -- Insert statements for procedure here        SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END

Example:

ALTER PROCEDURE SP_ShowTestData AS BEGIN        Select * from TestData; END GO

In above query you just needed to write the Alter and need to change the query.

Delete Stored Procedure:

Syntax:

DROP PROCEDURE <stored procedure name>;

Example:

DROP PROCEDURE SP_ShowTestData;

Above query will delete the SP from SQL database.

Execute Stored Procedure:

Syntax:

Exec <stored procedure name>;

Example:

Exec SP_ShowTestData;

If you want to pass parameter to stored procedure on that case you need to pass the value as shown below

Exec SP_ShowTestData 'name';

In above i have passed only one parameter to stored procedure.

YouTube Chanel Video: