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.

1. 

   Reply

   Hardono Arifanto said: 2007.08.30 23:38

   The download link for C# Lex:

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