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 several examples to resolve the SQL stored procedures in the database, so that the abstract things to visualize, easier to understand.
Example 1:
create proc Proc_stu
@sname varchar (),
@pwd varchar as
select * from Ren where sname= @sname and pwd = @pwd Go
See results: Proc_stu ' admin ', ' admin '
Example 2:
The following stored procedure implements user-validated functionality, returns 0 if unsuccessful, and returns 1 if successful.
CREATE PROCEDURE VALIDATE @USERNAME char (@PASSWORD char), @LEGAL BIT OUTPUT as
IF EXISTS (SELECT * from REN W Here sname = @USERNAME and PWD = @PASSWORD)
Select @LEGAL = 1
ELSE
Select @LEGAL = 0
The stored procedure is invoked in the program and the user is judged to be 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--a test for paging--you need to place the sort field in the first column (@FirstID nvarchar =null, the value of the first record in the current page @LastID nvarchar (2 0) =null,--the value of the sorted field for the last record in the current page @isNext bit=null,--true 1: Next; False 0: previous page @allCount int output,--Returns the total number of records @pageSize int ou
Tput,--Returns the number of records on one page @CurPage int--page number (page) 0: first page;-1 last page. As if @CurPage =0--represents the first page begin--Total Statistics Records select @allCount =count (ProductId) from product_test set @pageSize = 10--Returns the first page of the Data select Top ProductId, ProductName, Introduction the from Product_test order by ProductId end else if @CurPage =-1--represents Last page select * FROM (select top ProductId, ProductName, Introduction from Product_test ORDER by ProductId Desc) as a A order by ProductId else BEGIN if @isNext = 1--Turn to next page select Top ProductId, ProductName, Introduction from Product_te St where ProductId > @LastID ORDER by ProductId else--Turn to previous page select * FROM (select ProductId, ProductName, Intr Oduction from Product_test where ProductId < @FirstID ORDER BY ProductId desc) as BB order by ProductId End
The three examples mentioned above are typical examples of SQL stored procedures, and I hope that we can learn all the things we need.