SQL Server stored procedures and some basic usage

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_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 login name 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 ';

--Renaming 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 ' name like ' sp% ';

Ø user-defined stored procedures

1. Create syntax

create proc | Procedure Pro_name

[{@ parameter data type} [= default] [output],

{@ parameter data type} [= default value] [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_student go create proc Proc_get_student as SELECT * from student;

--Invoke, execute stored procedure exec proc_get_student;

3. Modify the stored procedure

--Modify the stored procedure alter proc proc_get_student AS SELECT * 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_stu go create proc proc_find_stu (@startId in T, @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 proc Pro C_findstudentbyname (@name varchar = '%j% ', @nextName varchar = '% ') as SELECT * from student where name like @name and name like @nextName; GO EXEC 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_getstudentrecord go create proc Proc_getstudentrec Ord

@id int,--Default input parameters

@name varchar out,--output parameters

@age varchar (output--) input and output parameters

As Select @name = name, @age = age from student where id = @id and sex = @age; Go--Declare @id 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 does not cache if (object_id (' proc_temp ', ' P ') is not null) drop proc proc_temp go create proc proc_temp with Recomp Ile as select * from student; GO EXEC proc_temp;

8. Encrypt stored Procedure

--Encryption 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_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_cursor go create proc proc_cursor

@cur cursor varying 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 (20),

@age int; exec proc_cursor @cur = @exec_cur output;--call stored procedure fetch next from @exec_cur into @id, @name, @age; while (@ @fetch_status = 0) begin to fetch the next from @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 a cursor

10. Paging Stored Procedure

---stored procedure, Row_number complete 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 between @startIndex and @endIndex go--drop proc pro_page exec pro_page 1, 4----Paging stored procedure if (objec t_id (' pro_page ', ' P ') is not null) drop proc pro_stu go CREATE PROCEDURE Pro_stu (

@pageIndex int,

@pageSize int) as declare @startRow int, @endRow int set @startRow = (@pageIndex-1) * @pageSize 1 Set @endRow = @startR ow @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

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 ', 16, 1); SELECT * from Sys.messages;

--Use the message defined in sysmessages RAISERROR (33003, 16, 1); RAISERROR (33006, 16, 1);