[MS SQL] 預儲程序(Store Procedure,SP) 常用整理

記錄最近用到的一些sp語法：方便以後查詢與改進。

SP中使用like查詢語句-使用字串變數串接：

note1:這邊新手要注意的是sql字串表示是 ' 號，所以 '' 號等於一個 '號
note2:字串長度宣告長度不足時，會被擷斷，網路有人說用max

declare @SQL nvarchar(500)
set @SQL  = 'select MovieCName from MovieDetail where MovieCName like ' + '''%' + @QueryTerm + '%'''
exec sp_executesql @SQL

SP中使用like查詢語句-使用查詢語法(非字串串接)：

select ..省略
WHERE (CourseName LIKE N'%' + RTRIM(LTRIM(@QueryFilter)) + '%' OR HomeWorkName LIKE N'%' + RTRIM(LTRIM(@QueryFilter)) + '%')

找出指定資料位於某一頁：

BEGIN
    ----搜尋目標位於的列索引
    set @FindMemberIDRowNo =
    (
        select top 1 rownum from
        (
            select Row_NUMBER() over (order by MemberID )as rownum,MemberID
            from Members
        ) as newMembers
        where MemberID=@MemberID
    )
    --找出頁索引
    select (count(*) / @pageSize + 1) as pageIndex from
    (select Row_NUMBER() over (order by MemberID )as rownum,MemberID from Members )
    as newMembers
    where newMembers.rownum < @FindMemberIDRowNo
 END

USE [MemberDB]
GO
DECLARE
@return_value int EXEC
@return_value = [dbo].[GetMemberRoleListPageIndex]    
@MemberID = 102,    
@PageSize = 5 SELECT
'Return Value' = @return_value
GO
搜尋會員代碼102的會員位於第幾分頁，因為每一頁大小為五。 步驟一： set @FindMemberIDRowNo會員所在的rownum為7

步驟二：

接著要找出頁索引，你可以先拿掉 (count(*) / @pageSize + 1)改為*，列出所有<@FindMemberIDRowNo的列數，看查詢結果為何?
 select *  from     (select Row_NUMBER() over (order by MemberID )as rownum,MemberID from Members )     as newMembers     where newMembers.rownum < @FindMemberIDRowNo  
從下圖可以明顯了解結果會取到rownum值6前的結果集。  
因此透過公式 (count(*) / @pageSize + 1) 找出 pageindex結果為2

使用while：

set @currNotices = 1   
while(@currNotices <= @PageSize)    
Begin         --do something           
End  

設定變數值:

declare @AssignID int set @AssignID =
 (           
select top(1) HomeWorkAssignID from #tempNotices where NoticesID=@currNotices     
  )

宣告一個資料表變數

declare @VarTable TABLE 
(  
編號 int IDENTITY(1,1) NOT NULL, 
名稱 char(20) 
)  

建立一個暫存的資料表，加#符號

Create Table #CourseIDList  
(       
IndexId int,        
CourseID int
)

查詢語法中的變數的字串加上N比對

(T1.L3PublishCommentClass = N'' + RTRIM(LTRIM(@CommentClass)) + '' )  

設定查詢結果欄位至變數

declare @TeacherID int
select @TeacherID = MemberID from Members where MemberNo = @TeacherNo
print @TeacherID