SQL SERVER PROCEDURES

Published on August 17 2015 by LakshmiSaahul,Dhana Royal

Customers Table

Contracts Table

Syntax

The syntax to create a stored procedure in SQL Server (Transact-SQL) is:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
   [ @parameter [type_schema_name.] datatype 
     [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
   , @parameter [type_schema_name.] datatype
     [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]

[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
[ FOR REPLICATION ]

AS

BEGIN
   [declaration_section]

   executable_section

END;

CREATE PROCEDURE SalesByCustomer
@CustomerName nvarchar(50)
AS
SELECT c.customer_name, sum(ctr.amount) AS TotalAmount
FROM customers c, contracts ctr
WHERE c.customer_id = ctr.customer_id
AND c.customer_name = @CustomerName
GROUP BY c.customer_name
ORDER BY c.customer_name
GO

EXEC SalesByCustomer 'CUSTOMER_1'
GO

DROP PROCEDURE

Once you have created your procedure in SQL Server (Transact-SQL), you might find that you need to remove it from the database.

Syntax

The syntax to a drop a stored procedure in SQL Server (Transact-SQL) is:

DROP PROCEDURE procedure_name;

Example

Let's look at an example of how to drop a stored procedure in SQL Server.

For example:

DROP PROCEDURE SalesByCustomer;

This DROP PROCEDURE example would drop the stored procedure called FindSite.

Advertising