PROCEDURE (Procedure) in SQL Server

Procedures in SQL Server What does it mean in programming? How to use SQL Server Procedures Is it difficult? Let's find out with Quantrimang.com!

Procedure is a program in a database of statements that you save for future use. In SQL Server, you can pass parameters to a procedure. Although it does not return a specific value like a function, it indicates whether the execution was successful or failed.

Benefits of using Procedures in SQL

• Easy editing: Programmers can easily edit code inside SQL Server procedures without restarting or deploying the application. For example, if a T-SQL query is written in an application and you need to change the logic, you must change the code in that application and redeploy it. Procedures in SQL Server eliminate those difficulties by storing code in the database. Therefore, when you want to change the logic inside a procedure, you can just do it with a simple Alter Procedure command.
• Reduce network access load: When using stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the entire T-SQL code.
• Can be reused: You can deploy stored procedures using multiple users or multiple client applications without rewriting code.
• Security: Reduce threats by removing direct access to the table.

This article will provide you with syntax and examples on how to create and delete procedures in SQL Server.

CREATE PROCEDURE (Create Procedure)

Syntax

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

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;

Parameter:

• schema_name: The schema name that owns the procedure.
• procedure_name: The name assigned to the procedure
• @parameters: One or more parameters are passed to the function.
• type_schema_name: Schema data type (if any).
• Datatype: Data type for @parameter.
• Default: Default value assigned to @parameter.
• OUT/OUTPUT: @parameter is an output parameter
• READONLY: @parameter cannot be overwritten by procedures.
• ENCRYPTION: The procedure's source code will not be stored as text in the system.
• RECOMPILE: The query will not be cached for this procedure.
• EXECUTE AS clause: Defines the security context for executing the procedure.
• FOR REPLICATION: The stored procedure will only be executed during replication.

For example

CREATE PROCEDURE spNhanvien
@nhanvien_name VARCHAR(50) OUT

AS
BEGIN
DECLARE @nhanvien_id INT;
SET @nhanvien_id = 8;
IF @nhanvien_id 

The above procedure is assigned the name spNhanvien, there is one parameter @nhanvien_name, The output of the parameter will be based on @nhanvien_id.

Then you can make the reference spNhanvien as follows:

USE [test]
GO

DECLARE @site_name varchar(50);
EXEC FindSite @site_name OUT;
PRINT @site_name;

GO

Drop Procedure

Once you have successfully created procedures, there will also be cases where you want to delete procedures from the database for several reasons.

Syntax

To delete a procedure, we have the following syntax:

DROP PROCEDURE procedure_name;

Parameter:

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

For example

DROP PROCEDURE spNhanvien;

Executing this command means you have just deleted the procedure spNhanvien from the database.

Temporary stored procedures

You can create temporary procedures similar to creating temporary tables. The tempdb database is used to create these procedures. You can divide temporary procedures into 2 types:

• Local temporary stored procedure
• Local interim procedures

Local Temporary Stored Procedures: You can create this type by using the # prefix and accessing it only during the session in which they were created. When the connection is closed, the process immediately ends.

Example of creating an internal temporary procedure:

CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

Global Stored Procedure: You can create this type by using ## as a prefix and accessing it from any session. When the connection used to create this process closes, the procedure will automatically end.

Example of creating a global temporary procedure:

CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

Limitations of SQL Server Procedures

• Debugging: Since debugging stored procedures has never been simple, you should not write and implement complex business logic using them. Otherwise, the results are prone to errors because they are not processed correctly.
• Dependent: As is known, professional DBA and database programmers handle “huge” data sets in large organizations. Application programmers are dependent on them because any small changes must be referred to the DBA – who can fix errors in existing procedures or build new ones.
• Expensive: Creating procedures in SQL Server is often expensive for DBA management because organizations have to pay extra for a professional DBA. DBA has expertise and high technology to handle complex database procedures better.
• Specialized for suppliers: Stored procedures written in one platform cannot run on other platforms. Because the procedure written in Oracle is more complex, you need to rewrite the entire procedure for SQL Server.

Previous article: FUNCTION in SQL Server

Next article: IF…ELSE command in SQL Server