SQL Server T-SQL stored procedures

Brief introduction

A stored procedure is a encapsulated process consisting of some SQL statements and control statements that reside in a database, can be called by a client application, or can be called from another procedure or trigger. Its parameters can be passed and returned. Similar to function procedures in an application, stored procedures can be called by name, and they also have input and output parameters

benefits of stored procedures :

1. Because the database executes the action, it is compiled and executed first. However, a stored procedure is a compiled block of code, so execution is more efficient than T-SQL statements.

2. A stored procedure can replace a large number of T-SQL statements when the program interacts with the network, so it can reduce the traffic of the network and improve the communication rate.

3. The stored procedures enable users who do not have permissions to access the database indirectly under control, thereby ensuring the security of the data.

Create a stored procedure

CREATE  proc[edure]  stored procedure name    @ Parameter 1  data type = default value output,    ...,    @ parameter n  data type = default value output    as SQL statement Go

1. A stored procedure that returns only a single record set

CREATE  PROC newfolder    as     select * FROM student    

Goexec NewFolder    --Call a stored procedure

2. Stored procedure with return value

int outputas insert into F_hsz (id,fid,uid) Select NEWID (), ' 1 ', ' 2 'setintexec Insetfile @returnrow Outputprint @returnrow

3. Stored procedures with input parameters and output parameters

int outputas declare @Nid varchar   --Stored Procedure Declaration TEMP Variable Select @Nid =id from fole where [email protected ] INSERT into F_hsz (id,fid,uid) Select NEWID (), ' 1 ', @Nidsetintexec insetfile ', @returnrow Outputprint @returnrow

4. Using things in stored procedures

Create proc Insetfile@retunrowintOutput asDECLARE @errorint=0--Error logging for operations in transactions declare @trancountintSet NOCOUNT on; --on indicates that the count SET NOCOUNT {on | is not returned OFF} Set Xact_abort on; --When a transaction is executed, if an error occurs, the transcation is set to the uncommittable state print @ @trancount beginTryBEGIN tran INSERT INTO F_HSZ (id,fid,uid) Select NEWID (), ' 1 ', ' $ ' insert into F_hsz (id,fid,uid) Select NEWID (), ' 1 ', 2Set@retunrow =1 Commit Tranreturn@retunrow EndTryBeginCatch   ifXact_state () =-1 begin rollback Tran; --Rolling back a transactionSet@retunrow =0return@retunrow End EndCatchSetXact_abort Off;go

Today's stored procedure ends here! Good night

SQL Server T-SQL stored procedures