Tạo con trỏ sql server

• Một số hàm ngày SQL
• Tạo function trả về DataTable trong SQL

* Cursor 1 table SQL

Declare @Cot1 nvarchar(50)
Declare @Cot2 nvarchar(50)

DECLARE Ten_cursor CURSOR FOR
SELECT Cot1,Cot2
FROM TenTable
WHERE ...

OPEN Ten_cursor

FETCH NEXT FROM Ten_cursor
INTO  @Cot1,@Cot2

WHILE @@FETCH_STATUS = 0
BEGIN
 
    print '   ' + CAST(@Cot1 as varchar(50))

    FETCH NEXT FROM Ten_cursor
    INTO @Cot1,@Cot2
    
END
CLOSE Ten_cursor ;
DEALLOCATE Ten_cursor ;

 

* Cursor table lồng table SQL

USE [HoSo]
GO
/****** Object:  StoredProcedure [dbo].[vdCursor2]    Script Date: 05/04/2023 20:35:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
-- [dbo].[vdCursor3]

ALTER PROCEDURE [dbo].[vdCursor3]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
Declare @Lop nvarchar(50)
Declare @TenLop nvarchar(50)


Declare @Child_ID int
Declare @Child_Ten nvarchar(50)
Declare @Child_Lop nvarchar(50)
Declare @Child_Diem int


DECLARE Lop_cursor CURSOR FOR
SELECT Lop,TenLop
FROM tbLop

OPEN Lop_cursor

FETCH NEXT FROM Lop_cursor
INTO @Lop, @TenLop

WHILE @@FETCH_STATUS = 0
BEGIN
 
    print '   ' + CAST(@TenLop as varchar(50))
    --print '   ' + @Diem
    -- Start CHILD CURSOR
    DECLARE HoSo_cursor CURSOR FOR
    SELECT ID,Ten,Lop,Diem
    FROM tbHoSo Where Lop=@Lop

    OPEN HoSo_cursor

    FETCH NEXT FROM HoSo_cursor
    INTO @Child_ID, @Child_Ten,@Child_Lop,@Child_Diem

    WHILE @@FETCH_STATUS = 0
    BEGIN
 
        print '            -' + CAST(@Child_Ten as varchar(50))
        --print '   ' + @Diem
        --   

        --
        FETCH NEXT FROM HoSo_cursor
        INTO @Child_ID, @Child_Ten,@Child_Lop,@Child_Diem
    
    END
    CLOSE HoSo_cursor ;
    DEALLOCATE HoSo_cursor ;
    
    -- End CURSOR CHILD
    FETCH NEXT FROM Lop_cursor
    INTO @Lop, @TenLop
    
END
CLOSE Lop_cursor ;
DEALLOCATE Lop_cursor ;



END