MS Sql 迴圈

ALTER proc [dbo].[sp_showDesignProducts]
as
/*宣告cursor*/
declare dpid_set CURSOR FOR
SELECT DISTINCT design_product_id FROM design_product

/*開啟cursor連結*/
open dpid_set

declare @dpid nvarchar(50)
/*清單將第一筆資料存入@dpid*/
fetch next from dpid_set into @dpid
/*檢查是否有資料，0代表有*/
while(@@fetch_status=0)
begin
/*取出每一筆專案id其所屬的上傳圖片的第一張圖*/
SELECT allResult.* FROM (
SELECT row_number() OVER (ORDER BY design_product_pictures_id) AS rid, * FROM design_product_pictures WHERE (design_product_pictures_pid = @dpid)) as allResult
where allResult.rid = 1
fetch NEXT from dpid_set into @dpid
end
/*關閉cursor連結*/
close dpid_set
/*移除cursor連結*/
deallocate dpid_set