羅必達工作室

SQL語法也是有迴圈的滴

CREATE PROCEDURE Shop_Paytype_Init
    @SM_SN as int
AS
BEGIN
   
    DECLARE @SP_SN as INT, @SP_Name as Varchar(50), @SP_State as INT, @SP_Orderby as INT
   
   
    DECLARE authors_cursor CURSOR FOR
    SELECT SP_SN, SP_Name, SP_State, SP_Orderby
    FROM System_Paytype
    ORDER BY SP_Orderby
   
    OPEN authors_cursor
   
    FETCH NEXT FROM authors_cursor INTO @SP_SN, @SP_Name, @SP_State, @SP_Orderby
   
    WHILE @@FETCH_STATUS = 0
    BEGIN
   
        IF NOT EXISTS(SELECT SP_SN FROM Shop_Paytype WHERE SM_SN=@SM_SN AND SP_SN=@SP_SN)
        BEGIN
            INSERT INTO Shop_Paytype(SM_SN, SP_SN, SP_CName, SP_CState, SP_COnOff, SP_COrderby)
            VALUES(@SM_SN, @SP_SN, @SP_Name, @SP_State, 0, @SP_Orderby)
        END
       
        FETCH NEXT FROM authors_cursor INTO @SP_SN, @SP_Name, @SP_State, @SP_Orderby
    END
   
    CLOSE authors_cursor
    DEALLOCATE authors_cursor


END