SQL Server: Procedures

Learn how to create and drop procedures in SQL Server (Transact-SQL) with syntax and examples.

What is a procedure in SQL Server?

In SQL Server, a procedure is a stored program that you can pass parameters into. It does not return a value like a function does. However, it can return a success/failure status to the procedure that called it.

Create Procedure

You can create your own stored procedures in SQL Server (Transact-SQL). Let’s take a closer look.

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;

schema_name

The name of the schema that owns the stored procedure.

procedure_name

The name to assign to this procedure in SQL Server.

@parameter

One or more parameters passed into the procedure.

type_schema_name

The schema that owns the data type, if applicable.

datatype

The data type for @parameter.

VARYING

It is specified for cursor parameters when the result set is an output parameter.

default

The default value to assign to @parameter.

OUT

It means that @parameter is an output parameter.

OUTPUT

It means that @parameter is an output parameter.

READONLY

It means that @parameter can not be overwritten by the stored procedure.

ENCRYPTION

It means that the source for the stored procedure will not be stored as plain text in the system views in SQL Server.

RECOMPILE

It means that a query plan will not be cached for this stored procedure.

EXECUTE AS clause

It sets the security context to execute the stored procedure.

FOR REPLICATION

It means that the stored procedure is executed only during replication.

Example

Let’s look at an example of how to create a stored procedure in SQL Server (Transact-SQL).

The following is a simple example of a procedure:

CREATE PROCEDURE FindSite
  @site_name VARCHAR(50) OUT

AS

BEGIN

   DECLARE @site_id INT;

   SET @site_id = 8;

   IF @site_id < 10
      SET @site_name = 'TechOnTheNet.com';
   ELSE
      SET @site_name = 'CheckYourMath.com';

END;

This procedure is called FindSite. It has one parameter called @site_name which is an output parameter that gets updated based on the variable @site_id.

You could then reference the new stored procedure called FindSite as follows:

USE [test]
GO

DECLARE @site_name varchar(50);

EXEC FindSite @site_name OUT;

PRINT @site_name;

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;

procedure_name

The name of the stored procedure that you wish to drop.

Example

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

For example:

DROP PROCEDURE FindSite;

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