31
32
33
34
35
.
.
.. 70
ra kết quả là
1 | 31...40
2| 41..50
3| 51..60
Code như sau:
USE [TN_DB] GO /****** Object: StoredProcedure [dbo].[sp_CauCoDinhFETCH_NEXT] Script Date: 01/05/2017 10:09:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_CauCoDinhFETCH_NEXT] AS BEGIN DECLARE @CH int DECLARE @De int DECLARE @monID int DECLARE @SoCau int DECLARE @i int SET @i = 1 DECLARE @j int SET @j = 0 SET @SoCau = 10 CREATE TABLE [dbo].[#IDCoDinh] /* tao bang tam dau vao */ ( [ID] [int] IDENTITY, [IDCauHoi] nvarchar(50) NULL ) CREATE TABLE [dbo].[#KetQua] /* tao bang tam ket qua */ ( [SoDe] [int] , [IDCauHoiCD] nvarchar(50) NULL ) /* insert vào bảng tạm để xử lý */ INSERT INTO [#IDCoDinh] (IDCauHoi) SELECT IDCau FROM CauHoi /* tạo ra con trỏ sử dụng trong fecth next*/ DECLARE cauhoiMon CURSOR FOR SELECT ID FROM [#IDCoDinh] OPEN cauhoiMon FETCH NEXT FROM cauhoiMon INTO @monID WHILE @@FETCH_STATUS = 0 BEGIN SET @CH = (SELECT IDCauHoi FROM #IDCoDinh WHERE ID = @monID) SET @De = @i SET @j = @j+1 BEGIN INSERT INTO #KetQua(SoDe,IDCauHoiCD) VALUES (@De,@CH) IF (@j = 10) BEGIN SET @i= @i+1 SET @j= 0 END END FETCH NEXT FROM cauhoiMon INTO @monID; END CLOSE cauhoiMon DEALLOCATE cauhoiMon; /* chuyển thành hàng ngang */ SELECT DISTINCT C2.SoDe, SUBSTRING( ( SELECT ','+ C1.IDCauHoiCD FROM #KetQua C1 WHERE C1.SoDe = C2.SoDe ORDER BY C1.SoDe FOR XML PATH ('') ), 2, 1000) DeCoDinh FROM #KetQua C2 END /* EXEC [dbo].[sp_CauCoDinhFETCH_NEXT] */chúc các bạn thành công