Several examples of SQL Server stored procedures

1. Common system stored procedures and usage syntax:
exec sp_databases; --View Database
exec sp_tables; --View Table
EXEC sp_columns student;--view columns
EXEC sp_helpindex student;--View index
EXEC sp_helpconstraint student;--constraints
exec sp_stored_procedures;
exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, definition statement
EXEC sp_rename Student, stuinfo;--modify the name of the table, index, column
exec sp_renamedb mytempdb, mydb;--change database name
exec sp_defaultdb ' master ', ' MyDB ';--Change the default database for logins
EXEC sp_helpdb;--database help, querying database information
exec sp_helpdb master;

2. User-defined stored procedures
2.1 Create a stored procedure without parameters:
if (exists (SELECT * from sys.objects where name = ' proc_get_student '))
drop proc Proc_get_student
Go
Create proc Proc_get_student
As
SELECT * from student;
--Call method
exec proc_get_student;

2.2 Stored procedure with input parameters
if (object_id (' Proc_find_stu ', ' P ') is not null)
drop proc Proc_find_stu
Go
Create proc proc_find_stu (@startId int, @endId int)
As
SELECT * from student where ID between @startId and @endId
Go
--Call method
EXEC Proc_find_stu 2, 4;

2.3 Stored procedures with input and output parameters
Create proc Getcommentcount
@newsid int,
@count int Output
As
Select @count =count (*) from Comment where [email protected]
--Call method
DECLARE @newsid int,
@count int;
Set @newsid = 7;
exec getcommentcount @newsid, @count output;
Select @count;
Print @count;

2.4 Functions that return a single value
Create function MyFunction
(@newsid int)
returns int
As
Begin
DECLARE @count int
Select @count =count (*) from Comment where [email protected]
return @count
End
--Call method
DECLARE @count int
EXEC @count =myfunction 2
Print @count

2.5 Paging Stored Procedures
--Stored procedure, Row_number complete paging
if (object_id (' pro_page ', ' P ') is not null)
drop proc Proc_cursor
Go
Create proc Pro_page
@startIndex int,
@endIndex int
As
Select COUNT (*) from product
;
SELECT * FROM (
Select Row_number () over (order by PID) as RowId, * from product
) Temp
where Temp.rowid between @startIndex and @endIndex
Go
--Call method
EXEC pro_page 1, 4

--Paging stored procedures
if (object_id (' pro_page ', ' P ') is not null)
drop proc Pro_stu
Go
CREATE PROCEDURE Pro_stu (
@pageIndex int,
@pageSize int
)
As
declare @startRow int, @endRow int
Set @startRow = (@pageIndex-1) * @pageSize +1
Set @endRow = @startRow + @pageSize-1
SELECT * FROM (
SELECT *, Row_number () over (order by ID ASC) as number from student
) T
where T.number between @startRow and @endRow;
--Call method
EXEC Pro_stu 2, 2;

Several examples of SQL Server stored procedures