SQL Server Stored Procedures

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 Procedure

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 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 the name of the table, index, column
exec sp_renamedb mytempdb, mydb;--change database name
exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins
EXEC sp_helpdb;--database help, querying database information
exec sp_helpdb master;

System Stored Procedure Example:

--Table renaming
exec sp_rename ' stu ', ' stud ';
SELECT * FROM stud;
--Rename naming
exec sp_rename ' stud.name ', ' sName ', ' column ';
exec sp_help ' stud ';
--Renaming an 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 * 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 value] [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

--Modifying stored procedures
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 (@startId 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

--A stored procedure with wildcard character parameters
if (object_id (' proc_findstudentbyname ', ' P ') is not null)
    drop proc Proc_findstudentbyname
Go
Create proc proc_findstudentbyname (@name varchar = '%j% ', @nextName varchar (20) = '% ')
As
    SELECT * FROM student the where name like @name and name @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_getstudentrecord (
    @id int,--Default input parameter
    @name varchar out, output parameters
    @age varchar (output--) input/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 @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 recompile
As
    SELECT * from student;
Go

exec proc_temp;

8. Encrypt stored procedures

if (object_id (' proc_temp_encryption ', ' P ') is 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 parameters

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
    FETCH 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 cursor

10. Paging Stored Procedure

---stored procedures, row_number complete pagination
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 (
        
    ) Temp
    where Temp.rowid between @startIndex and @endIndex
Go
--drop proc Pro_page
EXEC pro_page 1, 4
--
--Paging stored procedures
if (object_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 = @startRow + @pageSize-1
    SELECT * FROM (
        
    ) 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 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;
--Using the message defined in sysmessages
RAISERROR (33003, 16, 1);
RAISERROR (33006, 16, 1);


Reprinted from: http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html