What is a stored procedure
If you've been exposed to other programming languages, it's good to understand that stored procedures are like methods. He's the way he is. Then he has a similar method name, a method to pass the variable and return the result, so the stored procedure has a stored procedure name with a stored procedure parameter also has a return value. Advantages of stored procedures:
The ability to stored procedures greatly enhances the functionality and flexibility of the SQL language. Ensures the security and integrity of your data. Stored procedures enable users who do not have permissions to access the database indirectly under control, thus guaranteeing the security of the data. Stored procedures allow related actions to occur together to maintain the integrity of the database. Before running the stored procedure, the database has been analyzed by syntax and syntax, and the optimized execution scheme is given. This compiled process can greatly improve the performance of SQL statements. You can reduce the amount of traffic on your network. The operational procedures that embody enterprise rules are placed in the database server for centralized control. Stored procedures can be divided into system stored procedures, extended stored procedures and user-defined stored procedures system stored procedures Let's take a look at the system stored procedures, system stored procedures are defined by the system, mainly in the master database, the name starts with "SP" or "XP". 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. www.2cto.com 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;--constrained exec sp_helptext ' sp_stored_procedures ';--View the statement of the stored procedure creation definition exec sp_stored_procedures;exec sp_rename student , stuinfo;--change table name exec sp_renamedb mytempdb, mydb;--change database name exec sp_defaultdb ' master ', ' MyDB ';--Change the default database exec sp_ for logins helpdb;--database Help, querying database information exec sp_helpdb master;exec sp_attach_db--Attach database execsp_detach_db--Separate database Take a look at the specific code: &NBSP;EXEC sp_databases--See what database use myschoolexec sp_tables -- You can see that the Table_owner field shows the dbo confirmation that the user exec Sp_columns student--In addition to the system view can view the column, with the system stored procedure can also view to the column exec sp_helpindex student--view index, Can see the description of the index, after testing the discovery of the primary key is also a kind of exec sp_helpconstraint student--view constraints exec sp_helptext ' sys.all_columns '--View system view exec Sp_helptext ' sp_test '--View user-defined stored procedures EXEC sp_stored_procedures--View all stored procedures exec sp_rename ' student ', ' stuinfo '-- Change the table name use masterexec sp_renamedb ' MySchool ', ' school '--Change the database name, in order to change the success of the current database, you need to switch to another database exec sp_rename N ' student.idx_ Cid ', n ' Idx_cidd ', n ' index ';--rename index exec sp_helpdb--database help, query database information --Detach database use myschoolexec sp_detach_db ' test ';-- EXEC sp_attach_db --additional database exec sp_attach_db @dbname = ' Test ', @filename1 = ' D:\Program Files\Microsoft SQL Serv Er\mssql10. Mssqlserver\mssql\data\test.mdf ', @filename2 = ' D:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\test_log.ldf ' www.2cto.com user-defined stored procedures &NBSP; Before you create a stored procedure, say the name of the stored procedure, and see several articles about stored procedures like to add a prefix when creating stored procedures, it is important to develop the habit of prefixing the stored procedure name, although it is only a small thing, But often small details determine the big success or failure. See some people like this prefix, such as proc_ name. Also see this add-on prefix usp_ name. The former proc is a shorthand for procedure, and the latter sup means user procedure. I prefer the first one, so all the following stored procedure names are written in the first form. The name is written using the Camel name method. The syntax for creating a stored procedure is as follows: Create proc[edure] Stored procedure name @ parameter 1 [data type]=[default] [output] @ parameter 2 [data type]=[default] [output]as SQL statement EXEC procedure name [parameter] take a look at the various instances of different stored procedures: 45--Creating a stored procedure without parameters create PROCEDURE pro_studentas SELECT * FROM student;--execute stored procedure without parameters exec pro_student; --Modify stored procedure without parameters alter procedure pro_studentas SELECT * From student where sid>3;--executes the modified stored procedure EXEC pro_student; --Delete stored procedure drop procedure pro_student; -- Create a stored procedure with output parameters create proc Proc_getstudentrecord ( @sex varchar (2) out,--output parameters @age int output--Input Output Out parameters) www.2cto.com as SELECT * FROM student where ssex = @sex and sage = @age; --not cached in stored procedure use Myschool;create Procedure Proc_recompilestudentwith recompileas   SELECT * from student exec proc_recompilestudent --Encrypted stored procedure CREATE PROCEDURE Proc_ Encrptstudentwith encryptionas SELECT * from student; exec proc_recompilestudent stored procedure return value In the way 1, return a numeric type of stored procedure (not yet think of a method to return a string) if exists (SELECT * from sys.objects where name= ' proc_getscore0 ') drop procedure Proc_getscore0go create procedure proc_getscore0 ( @id int) asbegin DECLARE @score int Select @score =english from score where [email protected] IF (@score >60 ) return 0 else return 1end --Test call a stored procedure that returns a number <br& Gt;declare @t intexec @t = proc_getscore0 2select @t; --Here I have a small problem, if the return value is a string, when receiving declare @t nvarchar also error, then what should be done? --Not for the moment. 2, stored procedure for returning variables if exists (SELECT * from sys.objects where name= ' Proc_getscore ') drop procedure Pro C_getscorego www.2cto.com create ProcedurE proc_getscore @id int, @result varchar (outputasbegin ) DECLARE @score int Select @score =english from score where [email protected] IF (@score >60) &N Bsp Set @result = ' Pass ' ELSE Set @result = ' fail ' endgo--test one declare @id i Ntdeclare @temp varchar Set @id =3exec proc_getscore @id, @temp outputselect @temp Last example, using C #来调用具有返回值的存储过程, here I test by calling a stored procedure that returns a variable type. The test is done under the control table, the following two methods are written, the second better, the code is as follows: using system;using system.collections.generic;using system.linq;using System.text;using system.data;using system.data.sqlclient;namespace consoleapplication1{ class Program { static void Main (string[] args) { &NBSP ; //Method one //using (SqlConnection conn = new SqlConnection ("Serve R=.;d atabase=myschool;uid=sa;pwd=123456 ") //{ &N Bsp conn. Open (); // using (SqlCommand cmd = new SqlCommand ("Proc_getscore", C Onn) // { // cmd. CommandType = commandtype.storedprocedure; // cmd. Parameters.addwithvalue ("@id", 2); // sqlparameter SP = cmd. Parameters.Add ("@result", SqlDbType.VarChar, 50); www.2cto.com // &NBSP;SP. Direction = parameterdirection.output; // cmd. ExecuteNonQuery (); // &NBSP; Console.Write (sp. Value); // } //}&NBSP;&NBSP ; //Method two using (SqlConnection conn = new SqlConnection ("server=.; database=myschool;uid=sa;pwd=123456 ")) { &NBSP ; Conn. Open (); using (SqlCommand cmd = new SqlCommand ("Proc_getscore", con N) { CMD. CommandType = commandtype.storedprocedure; Sqlparamet Er[] Paras = { New SqlParameter (" @id ", SqlDbType.Int), &NBsp New SqlParameter ("@result", SqlDbType.NVarChar , }; &NBSP ; Paras[0]. Value = 2; PARAS[1]. Direction = parameterdirection.output; CMD. Parameters.addrange (paras); CMD. ExecuteNonQuery (); Console.Write (paras[1]. Value); } www.2cto.com &NBS P } Console.ReadLine (); }&NBSP ; }}
Stored procedure mechanisms for databases
