SQL stored procedure examples

SQL stored procedure examples

SQL storage is an important part of database operations. It is also abstract and difficult for beginners to understand. In this article, I will use several instances to parse the SQL stored procedures in the database, in this way, abstract things are visualized and easy to understand.

Example 1:

create proc proc_stu @sname varchar(20), @pwd varchar(20) as select * from ren where sname=@sname and pwd=@pwd go

View results: proc_stu 'admin', 'admin'

Example 2:

The following Stored Procedure implements the user verification function. If it fails, 0 is returned, and 1 is returned.

CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUTASIF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD) SELECT @LEGAL = 1 ELSE SELECT @LEGAL = 0

Call the stored procedure in the program and determine whether the user is LEGAL based on the value of the @ LEGAL parameter.

Example 3: an efficient paging data storage process can easily handle millions of data records

Create procedure pageTest -- used for paging testing -- the sorting field must be placed in the first column (@ FirstID nvarchar (20) = null, -- the value of the sorting field of the first record on the current page @ LastID nvarchar (20) = null, -- the value of the sorting field of the last record on the current page @ isNext bit = null, -- true 1: Next page; false 0: Previous Page @ allCount int output, -- total number of returned records @ pageSize int output, -- return the number of records on a page @ CurPage int -- page number (page number) 0: First page;-1 last page .) ASif @ CurPage = 0 -- indicates begin on the first page -- count the total number of records select @ allCount = count (ProductId) from Product_test set @ pageSize = 10 -- return the first page of Data select top 10 ProductId, ProductName, introduction from Product_test order by ProductId endelse if @ CurPage =-1 -- select * from (select top 10 ProductId, ProductName, Introductionfrom Product_test order by ProductId desc) as aa order by ProductIdelsebegin if @ isNext = 1 -- flip to the next page select top 10 ProductId, ProductName, introductionfrom Product_test where ProductId> @ LastID order by ProductId else -- go to the previous page select * from (select top 10 ProductId, ProductName, Introductionfrom Product_test where ProductId <@ FirstID order by ProductId desc) as bb order by ProductIdend

The three examples mentioned above are typical examples of SQL stored procedures. I hope you can learn what you need.

Articles you may be interested in:

• Sorting out the paging stored procedures of SQL Server 2000 and 2005
• SQL Server Stored Procedure syntax
• Instance for SQL Server to obtain the returned values of Stored Procedures
• SQL Server checks whether a table exists (such as the table name, function, and stored procedure)
• SQL Server Stored Procedures return values with transaction concatenation id
• SQL Server Stored Procedure dynamic parameter call implementation code
• SQL Server Stored Procedure page (sorted by multiple conditions)
• SQL Server Stored Procedure paging code
• Highlights of common SQL Server Stored Procedures
• SQL Server General paging Stored Procedure
• Compile the stored procedure in SQLServer 2005
• SQL Server 2005 columns all stored procedure statements
• Paging SQL Server Stored Procedure