Go Some common uses of SQL Server stored Procedures (things, exception snaps, loops)

A stored procedure in Transact-SQL, very similar to a method in the Java language, can be called repeatedly. After the stored procedure executes once, the statement can be cached so that the statements in the cache are used directly at the next execution. This can improve the performance of your stored procedures.

? The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

1, the advantages of stored procedures

A, stored procedure allows standard component-type programming

After a stored procedure is created, it can be invoked more than once in a program, without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, but have no effect on the application source code, which greatly improves the portability of the program.

B, the stored procedure can achieve faster execution speed

If an operation contains a large number of T-SQL statement code that is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives the storage plan in the system tables that are eventually present when a stored procedure is first run. A batch of T-SQL statements needs to be precompiled and optimized each time it is run, so it will be slower.

C, stored procedures to reduce network traffic

For the same operation on a database object, if the T-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client, the call statement is passed in the network, otherwise it will be multiple SQL statements. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to make full use of

The system administrator can restrict the execution of a stored procedure, which can restrict some data access, avoid unauthorized users ' access to the data, and ensure the security of the data.

? System stored Procedures

System stored procedures are system-created stored procedures designed to facilitate querying information from system tables or to complete administrative tasks or other system administration tasks related to updating database tables. System stored procedures are primarily stored in the master database and stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, we can still invoke system stored procedures in other databases. There are some system stored procedures that are automatically created in the current database when a new database is created.

Common system stored procedures are:

exec sp_databases; --View database exec sp_tables;        --View table exec sp_columns student;--view column exec sp_helpindex student;--view index exec sp_helpconstraint student;--constraint exec sp_stored_p Rocedures; exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, define statement exec sp_rename student, stuinfo;--Modify table, index, column name exec sp_renamedb mytempdb, mydb;--change the database name exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins exec sp_helpdb;--database help, query database information exec sp_helpdb Master

System Stored Procedure Example:

--Table rename exec sp_rename ' stu ', ' stud '; SELECT * FROM stud; --Rename named exec sp_rename ' stud.name ', ' sName ', ' column '; exec sp_help ' stud '; --Rename index exec sp_rename n ' student.idx_cid ', n ' Idx_cidd ', n ' index '; EXEC sp_help ' student '; --Query all stored procedures SELECT * from sys.objects where type = ' P '; SELECT * from sys.objects where type_desc like '%pro% ' and the name like ' sp% ';

? User-defined stored procedures

1. Create a grammar

create proc | Procedure Pro_name     [{@ parameter data type} [= default] [output],      {@ parameter data type} [= default] [output],      ...     ] as     sql_ Statements

2. Create a stored procedure with no parameters

--Create a stored procedure if (exists (SELECT * from sys.objects where name = ' proc_get_student '))     drop proc proc_get_student go create Proc Proc_get_student as     select * from student;--invoke, execute stored procedure exec proc_get_student;

3. Modifying stored Procedures

--Modify the stored procedure alter proc proc_get_student AS SELECT * from student;

4. Stored procedure with parameter

--with parameter stored procedure if (object_id (' Proc_find_stu ', ' P ') is not null)     drop proc proc_find_stu go create proc proc_find_stu (@startI d int, @endId int) as     select * from student where ID between @startId and @endId go EXEC proc_find_stu 2, 4;

5. Parameter stored procedure with wildcard characters

---with wildcard parameter stored procedure if (object_id (' proc_findstudentbyname ', ' P ') is not null)     drop proc proc_findstudentbyname go create Pro C Proc_findstudentbyname (@name varchar = '%j% ', @nextName varchar = '% ') as     select * from student where name L Ike @name and name like @nextName; GO EXEC proc_findstudentbyname; exec proc_findstudentbyname '%o% ', ' t% ';

6. Stored procedure with output parameters

if (object_id (' Proc_getstudentrecord ', ' P ') is not null)     drop proc Proc_getstudentrecord go create proc Proc_getstude Ntrecord (     @id int,--default input parameter     @name varchar () out,--output parameter     @age varchar () output--input/output parameter) as     SELECT @ Name = name, @age = age from  student where id = @id and sex = @age; go--  declare @id int,         @name varchar (20), c8/> @temp varchar (20); Set @id = 7;  Set @temp = 1; exec Proc_getstudentrecord @id, @name out, @temp output; Select @name, @temp; Print @name + ' # ' + @temp;

7. Do not cache stored procedures

--with RECOMPILE does not cache if (object_id (' proc_temp ', ' P ') is not null)     drop proc proc_temp go create proc proc_temp with R Ecompile as     select * from student; go EXEC proc_temp;

8. Encrypt stored procedures

--Encrypt with encryption  if (object_id (' proc_temp_encryption ', ' P ') are NOT null)     drop proc Proc_temp_encryption go CREATE proc proc_temp_encryption with encryption as     select * from student; go EXEC proc_temp_encryption; exec sp_helpt Ext ' proc_temp '; exec sp_helptext ' proc_temp_encryption ';

9. Stored procedure with cursor parameters

if (object_id (' proc_cursor ', ' P ') is not null)     drop proc proc_cursor go create proc proc_cursor     @cur cursor Varyin G Output as     set @cur = cursor forward_only static     for select ID, name, age from student;     Open @cur; Go--Call declare @exec_cur cursor; declare @id int,         @name varchar,         @age int, exec proc_cursor @cur = @exec_cur output;--call stored procedure fetch NEXT from @e Xec_cur into @id, @name, @age; while (@ @fetch_status = 0) The BEGIN     fetch next from the @exec_cur into @id, @name, @age;     print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name + ', Age: ' + convert (char, @age); End Close @exec_cur; Deallocate @exec_cur;--delete cursor

10. Paging Stored Procedure

---the stored procedure, Row_number completes the paging if (object_id (' pro_page ', ' P ') is not null)     drop proc proc_cursor go create proc pro_page     @ StartIndex int,     @endIndex int as     select COUNT (*) from product;         SELECT * FROM (         select Row_number () over (order by PID) as RowId, * from product      ) temp     where Temp.rowid betwee n @startIndex and @endIndex go--drop proc pro_page exec pro_page 1, 4----Paging stored procedure if (object_id (' pro_page ', ' P ') is not n ull)     drop proc pro_stu go CREATE PROCEDURE pro_stu (     @pageIndex int,     @pageSize int) as     declare @startRo w int, @endRow int     Set @startRow = (@pageIndex-1) * @pageSize +1     Set @endRow = @startRow + @pageSize-1     s Elect * FROM (         select *, Row_number () + (ORDER BY ID ASC) as number from student      ) T     where T.number BETW Een @startRow and @endRow; EXEC Pro_stu 2, 2;

? Raiserror

RAISERROR returns a user-defined error message, you can specify a severity level, and set the system variable to record the error that occurred.

The syntax is as follows:

Raiserror ({msg_id | msg_str | @local_variable}   {, severity, state}   [, argument[,... N]]   [with option[,... N]])

# msg_id: User-defined error information specified in the sysmessages system table

# msg_str: User-defined information with a maximum length of 2047 characters.

# severity: User defined The severity level associated with the message. When you use msg_id to raise a user-defined message created with sp_addmessage, the severity specified on RAISERROR overrides the severity defined in sp_addmessage.

Any user can specify a severity level of 0-18 directly. Only users who are commonly used by the sysadmin fixed server role or have ALTER trace permissions can specify a 19-25 direct severity level. The security level between 19-25 requires the WITH LOG option.

# state: Any integer from 1 to 127 directly. The state default value is 1.

RAISERROR (' is error ', 16, 1); SELECT * from Sys.messages; --Use the message defined in sysmessages RAISERROR (33003, 16, 1); RAISERROR (33006, 16, 1);

====================================----------------------------------------=================================== ===

database transaction processing in SQL Server is an important concept, but also a little bit difficult to understand, many SQL beginners write transaction processing code is often a vulnerability, this article describes three different methods, examples of how to write the correct code in the stored procedure transaction.

When writing SQL Server transaction-related stored procedure Code, you often see the following notation:

      BEGIN TRAN           UPDATE statement 1           ... UPDATE Statement 2           ... Delete Statement 3        ... Commit Tran

There is a great danger in writing SQL. Take a look at the following example:

   CREATE TABLE demo (ID int not null)     go to       begin tran        INSERT INTO demo values (NULL) INSERT INTO        demo values (2 )     Commit Tran     go

There is an error message that violates the NOT NULL constraint when executing, but then prompts (1 row (s) affected). After we executed the SELECT * from demo, we found that insert into demo values (2) was executed successfully. What is the reason for this? It turns out that SQL Server, when a runtime error occurs, rollback the statement that caused the error by default and continues with the subsequent statement.

How to avoid this problem? There are three ways to do this:

1. Precede the transaction statement with SET XACT_ABORT on

   Set XACT_ABORT on       BEGIN TRAN        UPDATE statement 1        ... UPDATE Statement 2        ... Delete Statement 3     ... Commit Tran     Go

When the Xact_abort option is on, SQL Server terminates execution and rollback the entire transaction when an error is encountered.

2. Once each individual DML statement executes, the execution status is immediately judged and handled accordingly.

   BEGIN TRAN        UPDATE statement 1          ... If @ @error <> 0 begin           Rollback tran           goto labend        End          DELETE statement 2          ... If @ @error <> 0 begin           Rollback tran           goto labend        End       Commit tran        labend:     Go

3. In SQL Server 2005, you can take advantage of the Try...catch exception handling mechanism

   BEGIN Tran       begin try        UPDATE statement 1        ... Delete Statement 2     ... End Try     begin catch        if @ @trancount > 0           rollback tran     end catch       if @ @trancount > 0        Commit t Ran     go

The following is a simple stored procedure that demonstrates the transaction process.

   CREATE PROCEDURE Dbo.pr_tran_inproc     as     begin        SET NOCOUNT on          BEGIN TRAN           UPDATE statement 1 ...             if @ @error <> 0 begin              Rollback TRAN              return-1           end             DELETE statement 2             ... If @ @error <> 0 begin              Rollback TRAN              return-1           end          Commit Tran          return 0     end     Go