A few simple examples of SQL stored procedures

reading: SQL storage is an important part of the database operation process, for some beginners is also more abstract difficult to understand, this article I will use a few examples to parse the database of SQL stored procedures, so that the abstract things visualized, more easily understood. Example 1:create proc proc_stu @sname varchar ( -), @pwd varchar ( -)  as Select* fromrenwhere[email protected] and pwd=@pwd Go View results: Proc_stu'Admin','Admin'Example 2: The following stored procedure implements user-authenticated functionality, returns 0 if unsuccessful, and returns 1 if successful. CREATE PROCEDURE VALIDATE @USERNAME CHAR ( -), @PASSWORD CHAR ( -), @LEGAL BIT outputas IF EXISTS (SELECT* from REN WHERE SNAME = @USERNAME and PWD =@PASSWORD) SELECT @LEGAL=1ELSE SELECT @LEGAL=0calls the stored procedure in the program and determines whether the user is legitimate based on the value of the @legal parameter. Example 3: An efficient data paging stored procedure can easily cope with millions of data CREATE PROCEDURE pagetest--test for page flipping--The sort field needs to be placed in the first column (@FirstID nvarchar ( -)=NULL, --The value of the sort field for the first record in the current page @LastID nvarchar ( -)=NULL, --The value of the sort field for the last record in the current page @isNext bit=NULL, --true 1: Next page;false 0: prev @allCountintOutput,--returns the total number of records @pageSizeintOutput,--returns the number of records on a page @CurPageint--page number (first page)0: first page;-1 last page. ) asif@CurPage =0--represents the first page begin--total number of statistics recordsSelect@allCount =count (ProductId) fromproduct_testSet@pageSize =Ten--return data on the first pageSelectTopTenProductId, ProductName, Introduction fromproduct_test ORDER by ProductId endElse if@CurPage =-1--represents the last pageSelect* from (SelectTopTenProductId, ProductName, Introduction fromProduct_test ORDER BY ProductId Desc) asAA ORDER BY ProductIdElsebeginif@isNext =1--turn to the next pageSelectTopTenProductId, ProductName, Introduction fromProduct_testwhereProductId >@LastID ORDER BY ProductIdElse--turn to the previous pageSelect* from (SelectTopTenProductId, ProductName, Introduction fromProduct_testwhereProductId < @FirstID ORDER BY ProductId Desc) asBB ORDER by ProductId end

A few simple examples of SQL stored procedures