14. SQL Server Stored Procedures

SQL Server Stored Procedures

Stored procedures are similar to functions that can be reused. Stored procedures are more powerful and more flexible than functions.

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

Benefits of Stored procedures:

1, the performance of the promotion

When a stored procedure executes, it is compiled and optimized for the first time. However, each execution of a batch T-SQL statement requires precompilation and optimization, so no stored procedures are fast.

2. Easy Maintenance

Stored procedures are stored in the database after they are created and can be executed by the program multiple calls. When you need to modify a stored procedure, there is no effect on the application code.

3. Security

The application only needs to call the stored procedure name, giving several parameters, rather than directly accessing the underlying object. Need to be given is not to delete and change the permissions, but the permissions of the exec.

System stored Procedures

System stored procedures are primarily stored in the master database, beginning with sp_, and can be used in all database objects.

Common system stored procedures

execSp_databases--View all DatabasesexecSp_tables--View all data sheetsexecSp_columns Student--View all columns of the student tableexecSp_helpindex Student--to view the index of a student tableexecSp_helpconstraint Student--to view constraints on a student tableexecSp_helptext'sp_databases' --view set to statementexecSp_rename Oldname,newname--Modify the name of a table, index, columnexecSp_renamedb webdb,newdb--Modify Database nameexecsp_helpdb webdb--View database Information

User-defined stored procedures

Grammar:

Create proc | procedureProc_name[{@parameter1 data_type} [=default] [Out | output],    {@parameter2Data_type}[=default] [Out | output]] as[begin]T-SQL code[End]

With no parameters

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procProc_test--creating create modify Alter as    Select *  fromStudentOrder  byIddesc--calledexecProc_test

The

Execute stored procedure uses the Execute keyword, which can be abbreviated to exec. Enhanced in Sql server 2012, you can modify the column names and types in the result set.

Execute  with result sets        (varchar(5),         varchar (ten),         varchar (2),         varchar (5),         varchar (5))    )

Input parameters

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procProc_test (@id int) as    Select *  fromStudentwhereId= @id--calledexecProc_testTen

Default parameters

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procProc_test (@id int = Ten) as    Select *  fromStudentwhereId= @id--calledexecProc_test--TenexecProc_test the -- the

Output parameters

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procProc_test (@id int,--Input Parameters    @name varchar(Ten) out,--Output Parameters    @age intOutput--input and OUTPUT parameters) asbegin --can write not write    Select @name =Name@age =Age fromStudentwhereId= @idEnd--calledDeclare @name varchar(Ten),@age intexecProc_testTen,@nameOut@ageOutputSelect @name,@age

Do not cache

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procproc_test withRecompile--do not cache, compile every time as    Select *  fromStudentOrder  byIddesc--calledexecProc_test

Encryption

if(exists(Select *  fromSys.objectswhereName= 'proc_test'))    Drop procProc_test--DeleteGoCreate procproc_test withEncryption--cannot view after encryption as    Select *  fromStudentOrder  byIddesc--calledexecproc_testexecsp_helptext proc_test--the text of the prompt object ' proc_test ' is encrypted. 

14. SQL Server Stored Procedures