A few simple examples of SQL stored procedures _mssql

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.