Simply, it Air Max 2012 Black Navy Blue White is a collection of stored procedures or multiple sql statements, it can be regarded as a batch file, but its role is not limited to the batch. Creates This part describes the variables, stored procedures and stored functions, calls, view, modify, and delete operations. One: Overview of SQL Server stored procedure stored procedure code written using T_SQL segment. Its purpose is to be able to easily query information from the system tables, and update database tables or perform related management tasks and other system management tasks .T_SQL statement is a SQL Server database and programming interface between applications. In many cases, some of the code written by developers repeated several times, each time to prepare if the same function code is not only cumbersome and error-prone, and because the statement is executed by one SQL Server will reduce the operating efficiency of the system. In 579756 403 Purple White Nike Black Mamba 24 Kobe Sale short, SQL Server stored procedure that is in order to achieve specific tasks, but will write some fixed operating statements require multiple calls into the program segments that are stored on the server program, there is a database server program to call. Advantages of the stored procedure: to speed up the system speed stored procedures, stored procedures, creating only when compiling, recompiling each subsequent execution when not needed. Stored procedures can encapsulate complex database operations, streamline operations, such as updates to multiple tables, delete. The modular design of the program can be achieved, a stored procedure can be called multiple times, to provide a unified database access interface, improved maintainability of the application. Stored procedures can increase security code for user objects stored procedure can not be directly referenced in the operation, SQL Server can Air Max 2011 Womens Grey Green set the user execute permissions on the specified stored procedure. Stored procedures can reduce network traffic, the stored procedure code is stored directly in the Air Max 2011 Womens Purple Grey database, the client and server communication process is not expected to generate a lot of traffic T_SQL codes. The disadvantage of the stored procedure: database migration is not convenient, stored procedures, and database management systems rely, SQL Server stored procedure encapsulated in the operation code can not directly portable to other database management systems. Does not support object-oriented design, you can not use an object-oriented way to encapsulate business logic, business logic, and even the formation of a common framework to support the service. Readability is poor, difficult to maintain. It does not support clustering. II: 1. The system stored procedure stored procedure classification system stored procedures are stored procedures provided by SQL Server system itself can be used as a command to perform various operations. System stored procedure is mainly used to obtain information, use the system stored procedure to complete the management of the database server from the system tables, provides help for the system administrator, for the user to view the database objects to provide convenient, system stored procedures in the database server, and to sp _ the beginning, the system stored procedure is defined in the system-defined and user-defined database, when you call, do not add the name of the database before the stored procedure is defined. For example: sp_rename system stored procedure can be modified to create an object in the current database user name, sp_helptext stored procedure can display text information rules, defaults, or view, SQL SERVER, many of the management server is done through the process of implementation of the system store Many system information can also execute system stored procedure to obtain. System stored procedure to create the system and stored in a master database, some of the system stored procedure can only be used by system administrators, and some system stored procedure authorized by other users to use. 2. The user stored procedure (custom stored procedures) custom stored procedure that is user T_SQL written statement, in order to achieve a specific business need, in the user database written statement T_SQL collection, custom stored procedures can accept input parameters, returned to the client results and information, to return output parameters. When you create a custom stored procedure, before the stored procedure name with '##' represents the adidas adiPure Crazyquick Collegiate Royal/White Q33301 Outlet creation of a global temporary stored procedures; previously stored procedure with '#' indicates that the local temporary stored procedure created. Local temporary stored procedure can only be used to create its answer at the end of the session, it will be deleted. Both stored procedures are stored in the tempdb database. User-defined stored procedure is divided into two categories: T_SQL and CLRT_SQL: stored procedures is the value of the saved set T_SQL statement, can accept and return user-supplied 586590-300 Nike Kobe 8 System Elite GC Poison Green Superhero Outlet parameters, the stored procedure New Nike Free 5.0 V4 Grey Blue Running Shoes may also return data from the database to Nike Heels Boots the client application. CLR stored procedure is a method of reference Microsoft.NET Framework common language of stored procedures can accept and return user-supplied parameters, they are .NET Framework assemblies as a public static method of the class to achieve. 3. extended stored procedures extended stored procedure is dynamically connected to the outside environment, execute SQL SERVER (DLL files) to achieve, can be loaded into SQL SERVER instance running in the address space execution, extended stored procedure can be extended stored procedures SQL SERVER API programming, extended stored procedure with the prefix 'xp_' to identify, for the user, extended stored procedures and Mandarin stored procedures can be performed in the same way. Three: Create a stored procedure we must first 2015 Nike Free 5.0 of its profits, prepare the following data: use sample_db; - create test books table create table books (book_id int identity (1,1) primary key, book_name varchar (20) , book_price float, book_auth varchar (10)); - insert the test data insert into books (book_name, book_price, book_auth) values ('The Analects', 25.6' Confucius'), ('Dragon', 25.6 'Jin Yong '), (' Snow Fox ', 32.7' Jin Yong '), (' Ordinary World ', 35.8' Lu Yao '), (' Records', 54.8 'Sima Qian'); 1. Create a stored procedure with no parameters - 1. Create a stored procedure with no arguments if (exists (select * from sys.objects where name = 'getAllBooks')) drop proc proc_get_studentgocreate procedure getAllBooksasselect * from books; - calls, to execute the stored procedure exec getAllBooks; 2 modify storage. Process alter procedure dbo.getAllBooks asselect book_auth from books; 3 Delete stored procedure drop procedure getAllBooks; 4 to rename the stored procedure sp_rename getAllBooks, proc_get_allBooks; 5 parameter to create a stored procedure with parameters stored procedures are divided into two types: Input parameters and output parameters Input parameters: for incoming values to the stored procedure, java language or the c the value passed similar. Output parameters: Used to call a stored procedure, the results of the participants, similar to java language passed by reference. Value is passed and passed by reference difference: the basic data types passed by value assignment belongs; assignment belongs passing references between reference types. Passed by value passed is the real value of the variable; passed passed by reference is a reference to the address of the object. After the value is passed, the two variables are the respective values change; after passed Air Jordan Heel by Nike Air Max reference, two references to change the status of the same object (1) with a stored procedure parameter if (exists (select * from sys.objects where name = 'searchBooks')) Nike Jordan Melo B Mo drop proc searchBooksgocreate proc searchBooks (bookID int) as - requirements book_id columns equal to the input parameters select * from books where book_id = @ bookID; - Executive searchBooksexec searchBooks 1; (2) with two parameters are stored process if (exists (select * from sys.objects where name = 'searchBooks1')) drop proc searchBooks1gocreate proc searchBooks1 (bookID int,bookAuth varchar (20)) as - requirements book_id and book_Auth columns equal to the input parameters select * from books where book_id = @ bookID and book_auth = @ bookAuth; exec searchBooks1 1, 'Jin Yong'; (3) create a stored procedure that returns a value if (exists (select * from sys.objects where name = 'getBookId')) drop proc getBookIdgocreate proc getBookId (bookAuth varchar (20), - input parameters, no defaultbookId int output - input / output parameter no default value) as select @ bookId = book_id from books where book_auth = @ bookAuth-- execution getBookId this with the return value of the stored procedure declareid int - declare a variable to receive the return value of the stored procedure is executed after the exec getBookId 'Confucius', @ id outputselectid as bookId; - the column value as is to return a name (4) create a stored procedure with wildcard if (exists (select * from sys.objects where name = 'charBooks')) drop proc charBooksgocreate proc charBooks (bookAuth varchar (20) = 'gold%',bookName varchar (20) = '%') as select * from books where book_auth likebookAuth and book_name likebookName; - execute a stored procedure charBooksexec charBooks 'hole%', Air Jordan Heel '% s'; (5) encrypting storage process with encryption clause is hidden from the user stored procedure text. The following example creates the encryption process, use sp_helptext system stored procedure to obtain information about the encryption process, and then try to obtain information about the process Air Jordan Outlet .if (object_id ('books_encryption' directly from the syscomments table, 'P') is not null) drop proc books_encryptiongocreate proc books_encryption with encryptionas select * from books; - perform this procedure books_encryptionexec books_encryption; exec sp_helptext 'books_encryption'; - console displays \u0026 quot; target 'books_encryption' encrypted text ʱ?? \u0026 Quot;. (6) does not cache --with recompile the stored procedure is not cached if (object_id ('book_temp', 'P') is not null) drop proc book_tempgocreate proc book_tempwith recompileas select * from books; goexec book_temp; exec sp_helptext 'book_temp '; (7) with the cursor to create a stored procedure if the parameter (object_id (' book_cursor ',' P ') is not null) drop proc book_cursorgocreate proc book_cursorbookCursor cursor varying outputas set @ bookCursor = cursor forward_only static for select book_id, book_name, book_auth from books openbookCursor; go-- stored procedure call book_cursor declarecur cursor,bookID int,bookName varchar (20),bookAuth varchar (20); exec book_cursor @ bookCursor = @ cur output; fetch next fromcur into @ bookID, @ bookName, @ bookAuth; while (@@FETCH_STATUS = 0) begin fetch next fromcur into @ bookID, @ bookName, @ bookAuth; print 'bookID:' + convert (varchar, @ bookID) + ', bookName:' +bookName + ', bookAuth:' + @ bookAuth; endclosecur - Close the cursor DEALLOCATEcur; - release the cursor (8) creates a page stored procedure if (object_id ('book_page',. 'P') is not Nike Free 5.0 V2 Women null) drop proc book_pagegocreate proc book_page (TableName varchar (50), - the table nameReFieldsStr varchar (200) = '*', - field name (all fields *)OrderString varchar ( 200), -! sort fields (fields do not have to support multi-plus order by)WhereString varchar (500) = N '', - a conditional statement (do not add where)PageSize int, - the number of records per page! PageIndex int = 1, - specify currently the first few pagesTotalRecord int output - returns the total number of records) asbegin - process start and end points DeclareStartRecord int; DeclareEndRecord int; DeclareTotalCountSql nvarchar (500) ; DeclareSqlString nvarchar (2000); setStartRecord = (@ PageIndex-1) * @ PageSize + 1 setEndRecord =StartRecord +PageSize - 1 SET @ TotalCountSql = N'selectTotalRecord = count (*) from '+TableName; - the total number of records in the statement SETSqlString = N' (select row_number () over (order by '+OrderString +') as rowId, '+ @ ReFieldsStr +' from '+TableName; - inquiry statement - IF (!WhereString = ''! orWhereString = null) BEGIN SET @ TotalCountSql = @ TotalCountSql + 'where' +WhereString; SETSqlString = @ SqlString + 'where' +WhereString; END - Article once do you get --IF (TotalRecord is null) - BEGIN EXEC sp_executesql @ totalCountSql, N '@ TotalRecord int out', @ TotalRecord output; - returns the total number of records - END ---- execute the main statement set SqlString = 'select * from' +SqlString + ') as t where rowId between' + ltrim (str (StartRecord)) + 'and' + ltrim (str (EndRecord)); Exec (SqlString) END- - call paging stored procedure book_pageexec book_page 'books', '*', 'book_id', '', 3,1,0; - declaretotalCount intexec book_page 'books', '*', 'book_id', '', 3,1, @ totalCount output; selecttotalCount as totalCount; - the total number of records.SQL Server-based: Stored Procedures