SQL Server creates stored procedures

A stored procedure in Transact-SQL, very similar to a method in the Java language, that can be called repeatedly. Once the stored procedure is executed once, the statement can be cached so that the statement in the cache is used directly the next time it executes. This will improve the performance of the stored procedure.

Ø The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled and stored in a database to complete a specific function, and are executed by specifying the name of the stored procedure and giving the 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 a database when they are created, stored procedures run faster than a single block of SQL statements. At the same time, it can reduce network traffic and simple network burden to a certain extent because it only needs to provide the stored procedure name and the necessary parameter information in the call.

1, the advantages of stored procedures

A, stored procedures allow standard component-type programming

After a stored procedure is created, it can be executed multiple times in a program without having to rewrite the SQL statement of the stored procedure. and database professionals can modify the stored procedures at any time, but have no effect on the application source code, thereby greatly improving the portability of the program.

B, the stored procedure can achieve faster execution speed

If an action contains a large number of T-SQL statement code that is executed multiple times, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives a storage plan in the system table that is ultimately present when a stored procedure is first run. The T-SQL statements for batches need to be precompiled and optimized each time, so the speed is slower.

C, stored procedures to reduce network traffic

For the same operation against a database object, if the T-SQL statement involved in the operation is organized into a stored procedure, when the stored procedure is invoked on the client, the network is passed only by the calling statement, otherwise it will be more than one SQL statement. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to fully utilize

A system administrator can restrict the execution of one stored procedure, thereby restricting access to certain data, preventing unauthorized users from accessing data, and ensuring data security.

Ø System Stored Procedures

A system stored procedure is a system-created stored procedure 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 are stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, we can call the system stored procedures in other databases. Some system stored procedures 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_ 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 the database name exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for login name exec sp_helpdb;--database help, Query database information exec sp_helpdb master;

System Stored Procedure Example:

--Table renaming exec sp_rename ' stu ', ' stud '; select * from stud;--rename named exec sp_rename ' stud.name ', ' sname ', ' column '; exec sp_help ' St UD ';--renaming index exec sp_rename n ' student.idx_cid ', n ' Idx_cidd ', n ' index '; exec sp_help ' student ';--Querying all stored procedures select * FROM sys.objects WHERE type = ' P '; select * sys.objects where Type_desc like '%pro% ' and name like ' sp% ';

Ø user-defined stored procedures

1. Create syntax

create proc | Procedure Pro_name [{@ parameter data type} [= default] [output], {@ parameter data type} [= default] [Output], ...] As Sql_statements

2. To create a stored procedure with no parameters

--Creates a stored procedure if (exists (SELECT * from sys.objects where name = ' proc_get_student ')) drop proc proc_get_studentgocreate proc PR Oc_get_studentas SELECT * from student;--invoke, execute stored procedure exec proc_get_student;

3. Modify the stored procedure

--Modify the stored procedure alter proc proc_get_studentasselect * FROM student;

4, with the parameter stored procedures

--with parameter stored procedure 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 = '%j% ', @nextName varchar = '% ') as SELECT * from student where name like @name a nd name like @nextName; goexec proc_findstudentbyname;exec proc_findstudentbyname '%o% ', ' t% ';

6, with output parameters stored procedures

if (object_id (' Proc_getstudentrecord ', ' P ') is not null) drop proc proc_getstudentrecordgocreate proc PROC_ Getstudentrecord (@id int,--The default input parameter @name varchar ()--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 (a), @temp varchar, set @id = 7; Set @temp = 1;exec Proc_getstudentrecord @id, @name out, @temp output;select @name, @temp;p rint @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_tempgocreate proc the Proc_tempwith Recompilea S select * from Student;goexec proc_temp;

8. Encrypt stored Procedure

--Encryption with encryption if (object_id (' proc_temp_encryption ', ' P ') are NOT null) drop proc proc_temp_encryptiongocreate proc PR Oc_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 parameter

if (object_id (' proc_cursor ', ' P ') is not null) drop proc proc_cursorgocreate proc proc_cursor @cur cursor varying outputas 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 ou tput;--invokes the stored procedure fetch next from @exec_cur into @id, @name, @age while (@ @fetch_status = 0) begin to fetch NEXT from @exec_cur int o @id, @name, @age; print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name + ', Age: ' + convert (char, @age); Endclose @exec_cur;d eallocate @exec_cur;--delete a cursor

10. Paging Stored Procedure

---stored procedure, Row_number complete paging if (object_id (' pro_page ', ' P ') is not null) drop proc proc_cursorgocreate proc Pro_page @startIndex int, @endIndex Intas select COUNT (*) from product; SELECT * FROM (select Row_number () over (order by PID) as RowId, * from product) temp where temp.rowid between @startInde X and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----Paging stored procedure if (object_id (' pro_page ', ' P ') is not null) drop proc Pro _stugocreate procedure Pro_stu (@pageIndex int, @pageSize int) as declare @startRow int, @endRow int set @startRow = (@page INDEX-1) * @pageSize +1 Set @endRow = @startRow + @pageSize-1 Select * FROM (SELECT *, Row_number () ASC) as number from student) T where T.number between @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 errors 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 message specified in the sysmessages system table

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

# severity: User-defined severity level associated with this message. When you use msg_id to raise a user-defined message that is created using sp_addmessage, the severity specified in sp_addmessage is overwritten by specifying the seriousness on the RAISERROR.

Any user can specify a 0-18 direct severity level. Only users who are common to 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 between 1 and 127 direct. The state default value is 1.

RAISERROR (' is error ', 1); the select * from sys.messages;--uses the message defined in sysmessages RAISERROR (33003, 1); RAISERROR (33006, 16, 1);