SQL Server Stored Procedure details

SQL Server Stored Procedure description 1. Basic syntax template for creating a stored procedure: if (exists (select * fromsys. objectswherenamepro_name) parameters @ param_nameparam_type [default_value] asbeginsql statement endps: [] indicates non-

SQL Server Stored Procedure explanation 1. basic syntax template for creating a stored procedure: if (exists (select * from sys. objects where name = 'pro_name ') drop proc pro_name go create proc pro_name @ param_name param_type [= default_value] as begin SQL statement end ps: [] indicates a non-

SQL Server Stored Procedure details

1. Create a basic syntax template for the stored procedure:

If (exists (select * from sys. objects where name = 'Pro _ name') drop proc pro_namegocreate proc pro_name @ param_name param_type [= default_value] asbegin SQL statement end

Ps: [] indicates non-mandatory content. Sys. objects stores information in the database, not only the table name, but also the stored procedure name, view name, and trigger.

For example:

 1 if (exists (select * from sys.objects where name = 'USP_GetAllUser')) 2     drop proc USP_GetAllUser 3 go 4 create proc USP_GetAllUser 5 @UserId int =1 6 as  7 set nocount on; 8 begin 9     select * from UserInfo where Id=@UserId10 end

Ps: the SQL Server utility interprets GO as a signal that the current Transact-SQL batch processing statement should be sent to SQL Server. The current batch processing statement is all the statements entered after the previous GO command. If it is the first GO command, it is all the statements from the beginning of a special session or script to the beginning of this GO command.

2. Call method:

exec dbo.USP_GetAllUser 2;

Ps: Generally, it is best to add the architecture name when executing the stored procedure, such as dbo. USP_GetAllUser, to reduce unnecessary system overhead and improve performance. If the schema name is not added before the stored procedure name, SQL SERVER first searches for the schema from the current Database sys schema (System Architecture). If the schema name is not found, it searches for other schemas, finally, search for the dbo architecture (System Administrator architecture.

3. View stored procedures in the database

Expand the database, programmable, and stored procedures in sequence to view the created stored procedures.

4. Modify the Stored Procedure

Alter proc proc_nameas SQL statement

5. Use of output parameters in Stored Procedures

 1 if (exists(select * from  sys.objects where name='GetUser')) 2     drop proc GetUser 3 go  4 create proc GetUser 5     @id int output, 6     @name varchar(20) out 7 as  8 begin  9     select @id=Id,@name=Name from UserInfo where Id=@id10 end11 12 go 13 declare 14 @name varchar(20),15 @id int;16 set @id=3;17 exec dbo.GetUser @id,@name out;18 select @id,@name;19 print Cast(@id as varchar(10))+'-'+@name;

Ps: the output parameter can be output.

6. Stored Procedure for retrieving data by PAGE

 1 if (exists(select * from  sys.objects where name='GetUserByPage')) 2     drop proc GetUserByPage 3 go  4 create proc GetUserByPage 5     @pageIndex int, 6     @pageSize int 7 as  8 declare  9 @startIndex int,10 @endIndex int;11 set @startIndex =  (@pageIndex-1)*@pageSize+1;12 set @endIndex = @startIndex + @pageSize -1 ;13 begin 14     select Id,Name from 15     (16         select *,row_number()over (order by Id)as number from UserInfo  17     )t where t.number>=@startIndex and t.number<=@endIndex18 end19 20 go 21 exec dbo.GetUserByPage 2,4;

7. Create a transaction in the Stored Procedure

If (exists (select * from sys. objects where name = 'jayjaytotest') drop proc JayJayToTestgo create proc JayJayToTest @ GiveMoney int, @ UserName nvarchar (20) as beginset nocount on; begin tran; begin try update BankTest set Money = Money-@ GiveMoney where Name = @ UserName; update BankTest set Money = Money + @ GiveMoney where Name = 'test'; commit; end try begin catch rollback tran; print ('exception occurred, transaction rollback '); end catch endgoexec JayJayToTest 10, 'jayjay'

8. Understand the execution plan of the stored procedure

SELECT * FROM sys. [syscacheobjects] view the execution plan of the current Cache

If the stored procedure passes the parsing phase successfully, the Microsoft SQL Server Query Optimizer analyzes the statements in the stored procedure and creates an execution plan. The execution plan describes the fastest way to execute a stored procedure, based on the following information:

1. Data volume in the table.

2. The existence and features of the table index and the distribution of data in the index column.

3. The comparison operator and comparison value used by the WHERE clause condition.

4. Whether a join exists and the keyword "UNION", "group by", and "order by" exists.

After analyzing these factors in the stored procedure, the query optimizer places the execution plan in the memory. The process for analyzing stored procedures and creating execution plans is called compilation. The execution plan in the optimized memory is used to execute the query. The execution plan will reside in the memory until the SQL Server or other objects need to be restarted. If the stored procedure is subsequently executed and the existing execution plan remains in the memory, SQL Server will reuse the existing execution plan. If the execution plan is no longer in memory, a new execution plan is created.

Re-compile the Execution Plan (create proc JayJayToTest with recompile)

When creating a stored procedure, specify the with recompile option in its definition, indicating that SQL Server will not cache the Stored Procedure plan at high speed; the stored procedure will be re-compiled upon each execution. The with recompile option can be used when the parameter values of a stored procedure vary greatly between different executions, resulting in different execution plans to be created each time. This option is not commonly used because it must be re-compiled every time a stored procedure is executed, which slows down the execution of the stored procedure.

Due to the new status of the database, some changes in the database may cause the execution plan to be inefficient or no longer valid. SQL Server detects these changes that make the execution plan invalid and marks the plan as invalid. After that, you must re-compile the new plan for the next connection that executes the query. Plan invalidation may be caused:

1. Make any structural changes (alter table and alter view) to the TABLE or VIEW referenced by the query ).
2. explicitly generate or automatically generate new distribution STATISTICS using statements (such as update statistics.
3. Remove the indexes used by the execution plan.
4. Call sp_recompile explicitly.
5. A large number of key changes (modifications made by other users to the tables referenced by the query using the INSERT or DELETE Statement ).
6. For tables with triggers, the number of rows in the inserted or deleted tables increases significantly.