Create Stored Procedure(初级学习记录)

存储过程初始学习：恰好项目中的表直接拿来用了

----Stored Procedure
----1.创建不带参数存储过程
if(exists(Select * From sys.objects Where name='sp_Item'))
    Drop proc sp_Item
go 
Create procedure sp_Item
as
    Select ti.ID,p.Product,p.Software,tc.CaseName,ti.TestResult,ti.TestValue,ti.DmsIssue 
    From TestItem ti
    INNER JOIN TestCase tc ON tc.ID=ti.CaseID
    INNER JOIN Products p ON p.ID=ti.ItemID

exec sp_item

----2. 创建带参数存储过程
if(object_id('sp_item_tid','P') is not null)
    drop proc sp_item_tid
go
Create procedure sp_item_tid(@beginId int,@endId int)
as
    Select ti.ID,p.Product,p.Software,tc.CaseName,ti.TestResult,ti.TestValue,ti.DmsIssue 
    From TestItem ti
    INNER JOIN TestCase tc ON tc.ID=ti.CaseID
    INNER JOIN Products p ON p.ID=ti.ItemID
    Where ti.ID between @beginId and @endId
go
exec sp_item_tid 1,5

----3. 带输出参数存储过程 
if(object_id('sp_item_output','P') is not null)
    drop proc sp_item_output
go
Create proc sp_item_output(@tid int,@show varchar(100) output)
as
    Select @show=Product+' | '+Software
    From Products
    Where ID=@tid
go

----exec
declare @out nvarchar(100)
exec sp_item_output 1,@out output
Select @out Show

----4. 分页存储过程 

----4.1 存储过程、row_number完成分页
if(object_id('sp_page','P') is not null)
    drop proc sp_page
go
Create proc sp_page(@startIndex int, @endIndex int)
as
    Select * From 
    (
        Select row_number() over(order by ti.ID) as rowId,
               ti.ID,p.Product,p.Software,tc.CaseName,ti.TestResult,ti.TestValue,ti.DmsIssue 
        From TestItem ti
        INNER JOIN TestCase tc ON tc.ID=ti.CaseID
        INNER JOIN Products p ON p.ID=ti.ItemID
    ) tb
Where tb.rowId between @startIndex and @endIndex
go
exec sp_page 1, 4
    
----4.2 分页存储过程
if(object_id('sp_page','P') is not null)
    drop proc sp_page
go
Create proc sp_page(@pageIndex int, @pageSize int)
as
    declare @startRow int,@endRow int
    set @startRow=(@pageIndex-1)* @pageSize + 1
    set @endRow=@pageIndex+@pageSize-1
    Select * From 
    (
        Select row_number() over(order by ti.ID) as rowId,
               ti.ID,p.Product,p.Software,tc.CaseName,ti.TestResult,ti.TestValue,ti.DmsIssue 
        From TestItem ti
        INNER JOIN TestCase tc ON tc.ID=ti.CaseID
        INNER JOIN Products p ON p.ID=ti.ItemID
    ) tb
Where tb.rowId between @startRow and @endRow
go
exec sp_page 1, 3
    
----Testing
Select row_number() over(order by ID) as rowId 
From TestItem

 

存储过程中用到的表：

--Product for arrrange product+SW
--Drop table Products;
Create table Products
(
ID int primary key identity,
Platform nvarchar(200),
Product nvarchar(200),
Software  nvarchar(200),
Benchmark  nvarchar(200),
isBenchmark bit default 'False',
Comment nvarchar(MAX),
)

--CaseList
Create table TestCase
(
ID int primary key identity,
CaseName  nvarchar(500),
KPI bit,
Target decimal(18,2),
OffTrack decimal(18,2), 
Unit nvarchar(20)
)

--CaseList test result
--drop table TestItem
Create table TestItem
(
ID  int primary key identity,
CaseID int foreign key references TestCase(ID),
ItemID int foreign key references Products(ID),
TestResult  nvarchar(100),
TestValue decimal(18,2),
DmsIssue nvarchar(200) 
)

在项目中应用存储过程。。。