PROCEDURE (Procedure) in SQL Server

Procedure is a program in a database of multiple statements that you save for later use. In SQL Server, you can pass parameters to the procedure, although it does not return a specific value as a function but indicates the successful or failed execution.

The article will give you the syntax and examples of how to create and delete procedures in SQL Server.

CREATE PROCEDURE

Syntax

To create a procedure in SQL Server, we use the following syntax:

 CREATE {PROCEDURE | PROC } [schema_name.]procedure_name 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 | RECOMPILE | EXECUTE AS Clause } ] EXECUTE AS Clause}] 
 [FOR REPLICATION] 

 AS 

 BEGIN 
 [declaration_section] 

 executable_section 

 END; 

Parameters:

1. schema_name: Schema name (schema) owns the procedure.
2. procedure_name: The assigned name for the procedure
3. @parameter: One or more parameters are passed into the function.
4. type_schema_name: Data type of schema (if any).
5. Datatype: Data type for @parameter.
6. Default: The default value assigned to @parameter.
7. OUT / OUTPUT: @parameter is an output parameter  
8. READONLY: @parameter cannot be overridden by the procedure.
9. ENCRYPTION: The source code of the procedure will not be stored as text in the system.
10. RECOMPILE: The query will not be cached (cache) for this procedure.
11. EXECUTE AS clause: Specifies the security context to execute the procedure.
12. FOR REPLICATION: The saved procedure will only be executed during the replication process.

For example

 CREATE PROCEDURE spNhanvien 
 @nhanvien_name VARCHAR (50) OUT 

 AS 

 BEGIN 

 DECLARE @nhanvien_id INT; 

 SET @nhanvien_id = 8; 

 IF @nhanvien_id <10 
 SET @nhanvien_name = 'Smith'; 
 ELSE 
 SET @nhanvien_name = 'Lawrence'; 

 END; 

The above procedure is named spNhanvien, there is a parameter of @nhanvien_name, the output of the parameter will be based on @nhanvien_id.

After that, you can perform the spNhanvien reference as follows:

 USE [test] 
 GO 

 DECLARE @site_name varchar (50); 

 EXEC FindSite @site_name OUT; 

 PRINT @site_name; 

 GO 

Drop Procedure

Once you've created the procedure successfully, there are also cases where you want to remove the procedure from the database for a few reasons.

Syntax

To remove a procedure, we have the following syntax:

DROP PROCEDURE procedure_name ;

Parameters:

procedure_name: The name of the procedure you want to delete .

For example

 DROP PROCEDURE spNhanvien; 

By executing this command, you have just deleted the spNhan procedure from the database.

Previous post: FUNCTION (Function) in SQL Server

Next lesson: IF . ELSE command in SQL Server