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 (20),
@pwd varchar (20)
As
select * from ren where [email protected] and [email protected]
Go

View results: Proc_stu ' admin ', ' admin '

Example 2:

The following stored procedure implements user-authenticated functionality, and if unsuccessful, returns 0, and succeeds returns 1.

CREATE PROCEDURE VALIDATE @USERNAME Char, @PASSWORD char, @LEGAL BIT OUTPUT
As

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

Calls 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 that can easily cope with millions of data

CREATE PROCEDURE pagetest--Test for page flipping
--you need to put the sort field 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
@allCount int output,--Returns the total number of records
@pageSize int output,--Returns the number of records on a page
@CurPage INT--page number (p. 0): first page;-1 last page.
)

As

If @CurPage =0--represents the first page
Begin
--Total records of statistics
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--represents the last page

SELECT * FROM
(select Top Ten 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 Ten ProductId,
ProductName,
Introduction
From Product_test where ProductId > @LastID ORDER by ProductId
Else
--turn to the previous page
SELECT * FROM
(select Top Ten ProductId,
ProductName,
Introduction
From Product_test where ProductId < @FirstID ORDER BY ProductId desc) as BB ORDER by ProductId
End

A few simple examples of SQL stored procedures