Several simple examples of sql stored procedures

Introduction: SQL storage is an important part of the database operation process. It is also abstract and difficult to understand for some beginners. In this article, I will use several examples to analyze the SQL stored procedures in the database, so that the abstract Things are visualized and easier 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 is unsuccessful, it returns 0, if it succeeds, it returns 1.

CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT
AS

IF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD)
SELECT @LEGAL = 1
ELSE
SELECT @LEGAL = 0

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

Example 3: An efficient data paging stored procedure can easily handle millions of data

CREATE PROCEDURE pageTest --Test for page turning
--The sort field needs to be placed in the first column

(
@FirstID nvarchar(20)=null, --The value of the sorting field of the first record in the current page
@LastID nvarchar(20)=null, --The value of the sorting field of the last record in the current page
@isNext bit=null, --true 1: next page; false 0: previous page
@allCount int output, --return the total number of records
@pageSize int output, --return the number of records on one page
@CurPage int --Page number (page) 0: first page; -1 last page.
)

AS

if @CurPage=0--indicates the first page
begin
--statistics of the total number of records
select @allCount=count(ProductId) from Product_test

set @pageSize=10
--Return the data on the first page
select top 10
ProductId,
ProductName,
Introduction
from Product_test order by ProductId
end

else if @CurPage=-1--Indicates the last page

select * from
(select top 10 ProductId,
ProductName,
Introduction

from Product_test order by ProductId desc ) as aa
order by ProductId
else

begin
if @isNext=1
--Turn to the next page
select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId > @LastID order by ProductId
else
--Turn to the previous page
select * from
(select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId
end

The three examples mentioned above are all typical SQL stored procedures. I hope you will study hard and learn what you need.