Detailed SQL Server stored procedures

transferred from: 62884658I. What is a stored procedure system stored procedure is a system-created stored procedure that is designed to easily query 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. Two. Stored procedure run process

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. depending on the type of return value, we can divide the stored procedure into three categories:

• The execution result of a stored procedure that returns a Recordset is a recordset, a typical example of retrieving records from a database that meet one or more criteria;
• Returns a value after the stored procedure for which the value is returned, such as executing a function or command with a return value in the database;
• The behavior stored procedure is used only to implement a function of the database, and there is no return value, such as update and delete operations in the database.

personally, the stored procedure is simply a bunch of SQL merges. The middle adds a bit of logic control.

1. However, stored procedures are more practical when dealing with complex business. For example

A complex data operation. If you're working at the front desk. Multiple database connections may be involved. But if you use stored procedures. It's only once. There are advantages in response time.

1. This means that stored procedures can give us the benefit of increased operational efficiency.

In addition, the program prone to BUG instability, and stored procedures, as long as the database does not appear to be a problem, basically there is no problem. In other words, a system that uses stored procedures is more stable in terms of security.So what's the problem, and when can I use storage? Is there no need for small projects where the volume of data is not very large and the business process is not very complex? Answer: wrong. Stored procedures are not just for large projects, but for small and medium-sized projects, it is also necessary to use stored procedures. Its power and advantages are mainly reflected in:

The
1. stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, and the general SQL statements are compiled once per execution, so using stored procedures can improve database execution speed.
3. stored procedures can be reused to reduce the workload of database developers.
4. High security, you can set only one of these users to have access to the specified stored procedure.
6. executes faster. There are two reasons: first, when the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.
7. stronger adaptability: Because the stored procedure accesses the database through a stored procedure, As a result, database developers can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.
8. Fabric work: the encoding of applications and databases can be done independently, without suppressing each other.

The use of stored procedures seems to have been an argument. I'm not inclined to use stored procedures as much as possible, so I think:

1. Run Speed: Most advanced database systems have statement cache, so the cost of compiling SQL has no effect. However, executing a stored procedure is more expensive than executing SQL directly (checking permissions, etc.), so there is no advantage to the stored procedure for very simple SQL.
2. Network load: If there are no multiple data interactions in the stored procedure, the network traffic is actually the same as the direct SQL.
3. Team development: Unfortunately, there is no better IDE tool for stored procedures than the mature IDE, which means that these must be done manually.
4. security mechanism: for the traditional C/s structure, the user connecting the database can be different, so the security mechanism is useful, but in the Web three-tier architecture, the database user is not for the user, So basically, there is only one user, with all the permissions (and at most one development user). At this point, the security mechanism is a bit superfluous.
5. user satisfaction: Actually this is only to access the database interface Unified, is the use of stored procedures, or EJB, not much, that is, in the three-tier structure, A separate data access layer is designed to achieve this goal.
6. Development debugging: As a result of the IDE's problems, the development and debugging of stored procedures is more difficult than the general program (the old version of DB2 can only use c write stored procedures, but also a disaster).
7. portability: Come on, this doesn't have to be, anyway. The general application is always bound to a database, otherwise it will not be able to optimize the database access to improve performance.
8. maintainability: Indeed, stored procedures are sometimes easier to maintain than programs because the db-side stored procedures can be updated in real-time, but in a 3-tier structure, Updating the server side of the data access layer can achieve this goal, but many platforms now do not support real-time updates.

Common system stored procedures are:

exec sp_databases;--View database exec sp_tables; --View table exec sp_columns student;--View columns exec sp_helpindex student;--View index exec sp_ Helpconstraint student;--constraints exec sp_stored_procedures; EXEC sp_helptext  "sp_stored_procedures";--View stored procedure creation, definition statement exec sp_rename student, stuinfo;--Modify table, index, column name exec sp_renamedb mytempdb, mydb;--change 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

create proc | procedure pro_name    [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ]as SQL_statements

2. Create a stored procedure with no parameters

--创建存储过程if (exists (select * from sys.objects where name = ‘proc_get_student‘)) drop proc proc_get_studentgocreate proc proc_get_studentas select * from student;--调用、执行存储过程exec proc_get_student;

3. Modifying stored Procedures

--修改存储过程alter proc proc_get_studentasselect * from student;

4. Stored procedure with parameter

--带参存储过程if (object_id(‘proc_find_stu‘, ‘P‘) is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as select * from student where id between @startId and @endIdgoexec 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_ Findstudentbynamegocreate proc Proc_findstudentbyname (@ Name varchar (20) =  '%j% ', @nextName varchar (20) =  '% ') as select * from student where name like @name and name Span class= "Hljs-keyword" >like @nextName; goexec proc_findstudentbyname;exec proc_findstudentbyname '%o% ', ' t% ';   

6. Stored procedure with output parameters

if (object_id (' Proc_getstudentrecord ',' P ') isNOT null) drop proc proc_getstudentrecordgocreate proc Proc_getstudentrecord (@idint,--Default input parameter@name varchar (Out,--output parameters@age varchar (output--input/output parameters) asSelect@name = name,@age = age from student where id =@idand sex =  @age; go--declare  @id Span class= "Hljs-keyword" >int,  @name varchar (20),  @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 不缓存if (object_id(‘proc_temp‘, ‘P‘) is not null) drop proc proc_tempgocreate proc proc_tempwith recompileas select * from student;goexec proc_temp;

8. Encrypt stored procedures

if (object_id(‘proc_temp_encryption‘, ‘P‘) is not null) drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas select * from student;goexec proc_temp_encryption;exec sp_helptext ‘proc_temp‘;exec sp_helptext ‘proc_temp_encryption‘;

9. Stored procedure with cursor parameters

if (object_id (' Proc_cursor ',' P ') isNOT null) drop proc proc_cursorgocreate proc Proc_cursor@cur cursor varying Outputas set@cur = cursor Forward_only staticForSelect ID, name, age from student;Open@cur; go--Call declare@exec_cur Cursor;declare@idInt@name varchar (20),@ageintEXEC proc_cursor@cur =@exec_cur output;--call stored procedure FetchNext from@exec_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, @ EndClose @exec_cur;d eallocate @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_cursorGocreate proc Pro_page @startIndexint, @endIndexIntAsSelectCOUNT (*)from product;SELECT *From (Select Row_number () over (Orderby PID)As RowId, *From Product Tempwhere Temp.rowid between @startIndexand @endIndexgo--drop proc Pro_pageEXEC pro_page1,4----Paging Stored proceduresif (object_id (' Pro_page ',' P ')IsNotNulldrop proc Pro_stuGoCreateProcedure Pro_stu (@pageIndexint, @pageSizeintas declare @startRow int, @endRow int set @startRow = (@pageIndex-1) * @pageSize +1 set @endRow = @startRow + @pageSize-1 select * from ( SELECT *, Row_number () over (order by ID ASC) as number  From student) T where t.number between @startRow and @endRow; exec pro_stu 2, 2;            

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

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.

任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。

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

raiserror(‘is error‘, 16, 1);select * from sys.messages;--使用sysmessages中定义的消息raiserror(33003, 16, 1);raiserror(33006, 16, 1);

Detailed SQL Server stored procedures