=========================== creating a stored procedure with no parameter and no return value ===========================
Create proc Pro_name
As
--The SQL statement to execute--
SELECT * FROM tablename
--Execute stored procedure--
EXEC pro_name
=========================== create a stored procedure with no return value for the parameter ===========================
--With parameter stored procedure
Create proc proc_find_stu (@startId int, @endId int)
As
SELECT * from student where ID between @startId and @endId
--Execute Stored procedure
EXEC Proc_find_stu 2, 4;
=========================== A stored procedure with a parameter return value ===========================
Create proc Findusernamebyid
(
@uID int, the condition parameter of the--SQL statement
@uName varchar Output--the parameter to be returned as the end of output
)
As
Select @uName =user_name from dbo. User_info WHERE user_id = @uID
-Execution
Begin
DECLARE @name varchar (100)
--this is assumed to be a known ID number
exec Findusernamebyid 3654, @name output
Select @name
End
--Output The corresponding user name after execution completes
=========================== Paged Stored Procedure ================================
-----------Create a paging stored procedure--------------
Create proc Selectdivicebypage
(
@pageIndex int,--page number
@pageSize int--single page record number
)
As
Declare
--Define the number of start and end lines per page
@startRow int= (@pageIndex-1) * @pageSize +1,
@endRow int = (@pageIndex-1) * @pageSize + @pageSize
--or as defined below
--declare @startRow int, @endRow int
--Set @startRow = (@pageIndex-1) * @pageSize +1
--Set @endRow = @startRow + @pageSize-1
--a list of required columns is listed here once, and unnecessary columns are not listed, thus increasing the execution rate of the query.
Select User_id,user_name,user_password,user_email
From (select Row_number () over (order by user_id ASC) as rownumber,* from dbo. User_info) A
where A.rownumber between @startRow and @endRow
---------perform a paging stored procedure------------
--Page 3, single page 10 records
EXEC selectdivicebypage 3,10
=========================== Modifying stored Procedures ===========================
ALTER PROC Proc_get_student
As
SELECT * from student;
=========================== Deleting a stored procedure ===========================
DROP PROCEDURE Proc_name
SQL Server simple usage of stored procedures