2007
08.30

SQL String Formatter (Part 2)

As I promised before, I will put the output of the programs that I mention in the previous post.

As input I will use the following SQL string (which I have verified that it is a valid SQL statement).

 ALTER PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name   sysname AS BEGIN     CREATE TABLE #aspnet_RoleMembers     (         Group_name      sysname,         Group_id        smallint,         Users_in_group  sysname,        User_id         smallint     );     INSERT INTO #aspnet_RoleMembers     EXEC sp_helpuser @name;     DECLARE @user_id smallint;    DECLARE @cmd nvarchar(500);    DECLARE c1 cursor FORWARD_ONLY FOR         SELECT User_id FROM #aspnet_RoleMembers;    OPEN c1;    FETCH c1 INTO @user_id;    WHILE (@@fetch_status = 0)    BEGIN        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';        EXEC (@cmd);        FETCH c1 INTO @user_id      END;     CLOSE c1;    DEALLOCATE c1  END

The output of the program are as follows:

  1. SQLinForm
    ALTER
    PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name sysname
    AS
            BEGIN
                    CREATE TABLE #aspnet_RoleMembers
                                    (
                                    Group_name sysname    ,
                                    Group_id smallint     ,
                                    Users_in_group sysname,
                                    User_id smallint
                                    );
                    INSERT INTO #aspnet_RoleMembers EXEC sp_helpuser @name;
                    DECLARE @user_id smallint;
                    DECLARE @cmd nvarchar(500);
                    DECLARE c1
                    cursor FORWARD_ONLY FOR
                            SELECT User_id FROM #aspnet_RoleMembers;
                            OPEN c1;
                            FETCH c1 INTO @user_id;
                    WHILE (@@fetch_status = 0)
                    BEGIN
                            SET @cmd =  'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
                            EXEC (@cmd);
                            FETCH c1 INTO @user_id
                    END;
                    CLOSE c1;
                    DEALLOCATE c1
            END
    

    Initially I thought there was missing ‘END’, but I found out that because I choose ‘Any SQL’ instead of ‘SQL Server’. Very impressive!!!

  2. SQL Online Formatter
    ALTER PROCEDURE [DBO].[ASPNET_SETUP_REMOVEALLROLEMEMBERS]
                   @name SYSNAME
    AS
      BEGIN
        CREATE TABLE #ASPNET_ROLEMEMBERS (
          GROUP_NAME     SYSNAME,
          GROUP_ID       SMALLINT,
          USERS_IN_GROUP SYSNAME,
          USER_ID        SMALLINT);
        
        INSERT INTO #ASPNET_ROLEMEMBERS
        EXEC SP_HELPUSER
           @name;
        
        DECLARE  @user_id SMALLINT;
        
        DECLARE  @cmd NVARCHAR(500);
        
        DECLARE C1 CURSOR FORWARD_ONLY FOR
        SELECT USER_ID
        FROM   #ASPNET_ROLEMEMBERS;
        
        OPEN C1;
        
        FETCH  C1
        INTO @user_id;
        
        WHILE (@@FETCH_STATUS = 0)
          BEGIN
            SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
            
            EXEC( @cmd);
            
            FETCH  C1
            INTO @user_id
          END;
        
        CLOSE C1;
        
        DEALLOCATE C1
      END
    
    

    As you see, it also performs as good as SQLinForm

  3. SQL Review
    ALTER PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name sysname AS 
    BEGIN
       
    CREATE TABLE #aspnet_RoleMembers (
          Group_name sysname,
          Group_id smallint,
          Users_in_group sysname,
          User_id smallint 
       );
       
    INSERT INTO #aspnet_RoleMembers 
    EXEC sp_helpuser @name;
       
    DECLARE
       @user_id smallint;
       
    DECLARE
       @cmd nvarchar(500);
       
    DECLARE
       c1 
    CURSOR FORWARD_ONLY 
    FOR 
    SELECT
       User_id 
    FROM
       #aspnet_RoleMembers;
       OPEN c1;
       FETCH c1 INTO @user_id;
       WHILE (@@fetch_status = 0) 
    BEGIN
       
    SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
       
    EXEC (@cmd);
       FETCH c1 INTO @user_id 
    END;
    CLOSE c1;
    DEALLOCATE c1 
    END
    

    Unfortunately there is no option to indent text between BEGIN and END. But the plus point is you can run it

  4. Pl/sql tidy
    Hmm.. doesn’t seem to work. Is it because it only parse PL/SQL? (I will investigate further when I really have nothing to do :P)

As for the LEX script, I’m still trying to get it work on C# using CsLEX, so it would take sometime before I post the result here.

GD Star Rating
a WordPress rating system

Incoming Search Term

Advertise Here

1 comment so far

Add Your Comment
  1. The download link for C# Lex:

    http://www.infosys.tuwien.ac.at/cuplex/cup.htm