T-SQL Recurcive stored procedure

The following T-SQL procedure gets users from Active Directory from scecified

group and populates to the tblMembersOfORG_AD_Recurcive_Groups table:

CREATE TABLE [dbo].[tblMembersOfORG_AD_Groups](
   [sAMAccountName] [nvarchar](256) NULL
   [userPrincipalName] [nvarchar](256) NULL
  ,[adspath] [nvarchar](400) NOT NULL
  ,[Department] [nvarchar](256) NULL
  ,[Title] [nvarchar](256) NULL
  ,[mail] [nvarchar](256) NULL
  ,[telephoneNumber] [nvarchar](256) NULL
  ,[sn] [nvarchar](256) NULL
  ,[givenName] [nvarchar](256) NULL
  ,[cn] [nvarchar](256) NULL
  ,[membership] [nvarchar](500) NULL
  ,[loginName] [nvarchar](256) NULL
  ,[DateCreated] [datetime] NULL
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

 PRINT 'drop procedure dbo.udpMembersOfORG_AD_Recurcive_Groups'
GO

if exists (select * from dbo.sysobjects
  where id = object_id(N'dbo.udpMembersOfORG_AD_Recurcive_Groups')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure dbo.udpMembersOfORG_AD_Recurcive_Groups
GO

PRINT 'CREATE PROC dbo.udpMembersOfORG_AD_Recurcive_Groups'
GO

/******************************************************************************
NAME: udpMembersOfORG_AD_Recurcive_Groups

PURPOSE: Gets users from Active Directory across DET from scecified
groups and populates to the tblMembersOfORG_AD_Recurcive_Groups table

REVISIONS: Call sample: exec dbo.udpMembersOfORG_AD_Recurcive_Groups 

           'LDAP://org.com/CN=ORG_DEPOSITOR,OU=Groups,DC=ORG,DC=com'
$Log$

Ver Date Author Description

******************************************************************************/
CREATE PROC [dbo].[udpMembersOfORG_AD_Recurcive_Groups]
@adPath nvarchar(500)
AS
BEGIN
DECLARE @UniversalGroup nvarchar(100)
       ,@dcName nvarchar(100)
       ,@memberOf nvarchar(500)
       ,@strSQL varchar(7500)
       ,@delim CHAR(1)
       ,@cursorName NVARCHAR(50)
       ,@sAMAccountName nvarchar(256)
       ,@userPrincipalName nvarchar(256)
       ,@adspath nvarchar(400)
       ,@Department nvarchar(256)
       ,@Title nvarchar(256)
       ,@mail nvarchar(256)
       ,@telephoneNumber nvarchar(256)
       ,@sn nvarchar(256)
       ,@givenName nvarchar(256)
       ,@cn nvarchar(256)
       ,@err int
       ,@CurrentDate datetime
DECLARE @tempADSI TABLE (sAMAccountName nvarchar(256)
       ,userPrincipalName nvarchar(256)
       ,adspath nvarchar(400)
       ,Department nvarchar(256)
       ,Title nvarchar(256)
       ,mail nvarchar(256)
       ,telephoneNumber nvarchar(256)
       ,sn nvarchar(256)
       ,givenName nvarchar(256)
       ,cn nvarchar(256))

  SET @CurrentDate = getdate() 

  SET @delim = '/'
  SET @dcName = SUBSTRING(@adPath,0,CHARINDEX('.win' + @delim, @adPath) + LEN('.win'))
  SET @memberOf = SUBSTRING(@adPath,CHARINDEX('.win' + @delim, @adPath) + LEN('.win') + 1,LEN(@adPath))
  SET @strSQL = 'DECLARE ADSI_cursor CURSOR
  FOR SELECT sAMAccountName
       ,userPrincipalName
       ,adspath
       ,Department
       ,Title
       ,mail
       ,telephoneNumber
       ,sn
       ,givenName
       ,cn
       FROM OPENQUERY( ADSI,'''+ 'SELECT sAMAccountName
       ,userPrincipalName
       ,adspath
       ,Department
       ,Title
       ,mail
       ,telephoneNumber
       ,sn
       ,givenName
       ,cn'+ ' FROM '''''
       + @dcName
       + ''''' WHERE memberOf=''''' + @memberOf
       + ''''''')'-- Fill table 

    EXEC(@strSQL)

    IF @@error = 0 BEGIN
      OPEN ADSI_cursor
  END

  FETCH NEXT FROM ADSI_cursor
  INTO @sAMAccountName
       ,@userPrincipalName
       ,@adspath
       ,@Departmen
       ,@Title
       ,@mail
       ,@telephoneNumber
       ,@sn
       ,@givenName
       ,@cn
       WHILE @@FETCH_STATUS = 0 BEGIN
              PRINT @adspath
              IF (@sn is not null) BEGINBEGIN TRANSACTION
                 INSERT INTO dbo.tblMembersOfORG_AD_Groups(
                     sAMAccountName
                     ,userPrincipalName
                     ,adspath
                     ,Department
                     ,Title
                     ,mail
                     ,telephoneNumber
                     ,sn
                     ,givenName
                     ,cn
                     ,membership
                     ,loginName
                     ,DateCreated)
                 VALUES(@sAMAccountName
                     ,@userPrincipalName
                     ,@adspath
                     ,@Department
                     ,@Title
                     ,@mail
                     ,@telephoneNumber
                     ,@sn
                     ,@givenName
                     ,@cn
                     ,@adPath
                     ,replace(substring(@adspath,0,charindex('.det.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
                     ,@CurrentDate)
                 COMMIT
              END
              INSERT INTO @tempADSI(sAMAccountName
                     ,userPrincipalName
                     ,adspath
                     ,Department
                     ,Title
                     ,mail
                     ,telephoNumber
                     ,sn
                     ,givenName
                     ,cn)
              VALUES(@sAMAccountName
                     ,@userPrincipalName
                     ,@adspath
                     ,@Department
                     ,@Title
                     ,@mail
                     ,@telephoneNumber
                     ,@sn
                     ,@givenName
                     ,@cn)

             FETCH NEXT FROM ADSI_cursor
             INTO @sAMAccountName
                     ,@userPrincipalName
                     ,@adspath
                     ,@Department
                     ,@Title
                     ,@mail
                     ,@telephoneNumber
                     ,@sn
                     ,@givenName
                     ,@cn
          END
          IF @@error = 0 BEGIN
             CLOSE ADSI_cursor
          END
          IF @@error = 0 BEGIN
             DEALLOCATE ADSI_cursor
          END
          IF @@error = 0 BEGIN
             DEALLOCATE DOMAIN_cursor
             DECLARE DOMAIN_cursor CURSOR FOR
                select sAMAccountName
                     ,userPrincipalName
                     ,adspath
                     ,Department
                     ,Title
                     ,mail
                     ,telephoneNumber
                     ,sn
                     ,givenName
                     ,cn
              from @tempADSI
          END
          IF @@error = 0 BEGIN
             OPEN DOMAIN_cursor
          END
          FETCH NEXT FROM DOMAIN_cursor
          INTO @sAMAccountName
                     ,@userPrincipalName
                     ,@adspath
                     ,@Department
                     ,@Title
                     ,@mail
                     ,@telephoneNumber
                     ,@sn
                     ,@givenName
                     ,@cn
          WHILE @@FETCH_STATUS = 0 BEGIN
                IF (@sn is null) BEGIN
                     -- Recurcive call
                     exec dbo.udpMembersOfORG_AD_Recurcive_Groups @adspath
                END
                IF @@error = 0 BEGIN
                   FETCH NEXT FROM DOMAIN_cursor
                   INTO @sAMAccountName
                     ,@userPrincipalName
                     ,@adspath
                     ,@Department
                     ,@Title
                     ,@mail
                     ,@telephoneNumber
                     ,@sn
                     ,@givenName
                     ,@cn
                END
          END
     END
GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON
GO

If you find this script useful please donate generously.