错误的语法:"create view必须是批处理中仅有的语句"

 编写脚本提示：

错误的语法:"create view必须是批处理中仅有的语句"

 

IF NOT EXISTS ( SELECT  1
                FROM    sys.views
                WHERE   name = 'v_CS_UserRoleNames' )
    BEGIN
        CREATE VIEW v_CS_UserRoleNames
        AS
            SELECT  c.UserID AS ur_UserID ,
                    c.LoginID AS ur_LoginID ,
                    c.FullName AS ur_FullName ,
                    c.[Status] AS ur_Status ,
                    c.ZoneID AS ur_ZoneID ,
                    c.OrgID AS ur_OrgID ,
                    c.FactID AS ur_FactID ,
                    ','
                    + STUFF(( SELECT    ',' + b.RoleName
                                        + CAST(b.RoleMajorType AS VARCHAR)
                              FROM      System_Role b
                              WHERE     CHARINDEX(','
                                                  + CONVERT(VARCHAR(36), b.RoleID)
                                                  + ',', ',' + c.RoleIDs + ',') > 0
                            FOR
                              XML PATH('')
                            ), 1, 1, '') + ',' AS ur_RoleNames
            FROM    System_Users c
    END

ELSE
    BEGIN
 
        ALTER VIEW v_CS_UserRoleNames
        AS
            SELECT  c.UserID AS ur_UserID ,
                    c.LoginID AS ur_LoginID ,
                    c.FullName AS ur_FullName ,
                    c.[Status] AS ur_Status ,
                    c.ZoneID AS ur_ZoneID ,
                    c.OrgID AS ur_OrgID ,
                    c.FactID AS ur_FactID ,
                    ','
                    + STUFF(( SELECT    ',' + b.RoleName
                                        + CAST(b.RoleMajorType AS VARCHAR)
                              FROM      System_Role b
                              WHERE     CHARINDEX(','
                                                  + CONVERT(VARCHAR(36), b.RoleID)
                                                  + ',', ',' + c.RoleIDs + ',') > 0
                            FOR
                              XML PATH('')
                            ), 1, 1, '') + ',' AS ur_RoleNames
            FROM    System_Users c
    END 

 

 

create view 必须是批处理中的第一条语句。

修改为：

IF NOT EXISTS ( SELECT  1
                FROM    sys.views
                WHERE   name = 'v_CS_UserRoleNames' )
    BEGIN
        DROP VIEW v_CS_UserRoleNames
    
    END

go
CREATE VIEW v_CS_UserRoleNames
AS
    SELECT  c.UserID AS ur_UserID ,
            c.LoginID AS ur_LoginID ,
            c.FullName AS ur_FullName ,
            c.[Status] AS ur_Status ,
            c.ZoneID AS ur_ZoneID ,
            c.OrgID AS ur_OrgID ,
            c.FactID AS ur_FactID ,
            ','
            + STUFF(( SELECT    ',' + b.RoleName
                                + CAST(b.RoleMajorType AS VARCHAR)
                      FROM      System_Role b
                      WHERE     CHARINDEX(',' + CONVERT(VARCHAR(36), b.RoleID)
                                          + ',', ',' + c.RoleIDs + ',') > 0
                    FOR
                      XML PATH('')
                    ), 1, 1, '') + ',' AS ur_RoleNames
    FROM    System_Users c

 

转载于:https://www.cnblogs.com/Zev_Fung/p/8135737.html