-------------------------------------------------------------------- Listing 3 -------------------------------------------------------------------- USE master GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_FetchRelatedRows') DROP PROCEDURE dbo.sp_FetchRelatedRows GO CREATE PROCEDURE dbo.sp_FetchRelatedRows @DBChain varchar(2000), @DBWhere varchar(2000) = NULL, @DBAdmin int = NULL, @DBField varchar(100) = NULL, @DBValue int = NULL, @PCField varchar(100) = NULL, @PCValue int = NULL AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 DECLARE @DBAT varchar(40) DECLARE @Task varchar(8000) DECLARE @Bank varchar(8000) DECLARE @Cash varchar(8000) DECLARE @Loan varchar(800) DECLARE @Name varchar(100) DECLARE @Same varchar(100) DECLARE @PKey varchar(100) DECLARE @ZKey varchar(100) DECLARE @Lock varchar(100) DECLARE @Link varchar(100) DECLARE @Work int DECLARE @Wish int DECLARE @Rows int SET @DBAT = '##FetchRows' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5) SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAT + CHAR(39) + ') DROP TABLE ' + @DBAT EXECUTE (@Task) CREATE TABLE #Work (Work int IDENTITY(1,1), Name varchar(100), PKey varchar(100), Bank varchar(7700)) SET @Wish = 1 SET @Work = CHARINDEX('|',(@DBChain)+'|') WHILE @Work > 0 BEGIN SET @Name = SUBSTRING(@DBChain,@Wish,@Work-@Wish) INSERT #Work (Name) VALUES (@Name) SET @Wish = @Work + 1 SET @Work = CHARINDEX('|',(@DBChain)+'|',@Wish) END UPDATE #Work SET PKey = C.name FROM #Work AS T JOIN sysobjects AS O ON O.name = @TPre + T.Name JOIN syscolumns AS C ON C.id = O.id AND C.colid = 1 SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain SELECT @Rows = COUNT(*) FROM #Work IF @DBWhere IS NOT NULL BEGIN SET @Cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32)) SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39)) SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32)) SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32)) END SET @Work = 1 SELECT @Name = Name, @PKey = PKey FROM #Work WHERE Work = @Work SET @Lock = @TPre + @Name SET @Bank = @TPre + @Name + ' WITH (NOLOCK)' SET @Task = 'SELECT * INTO ' + @DBAT + ' FROM ' + @Bank + ' WHERE ' + CASE WHEN @DBWhere IS NULL THEN '0 = 0' ELSE '(' + @Cash + ')' END UPDATE #Work SET Bank = @Bank WHERE Work = @Work IF @DBField IS NOT NULL AND @DBValue IS NOT NULL BEGIN IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @Lock AND C.name = @DBField) SET @Task = @Task + ' AND ' + REPLACE(@DBField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@DBValue) END IF @PCField IS NOT NULL AND @PCValue IS NOT NULL BEGIN IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @Lock AND C.name = @PCField) SET @Task = @Task + ' AND ' + REPLACE(@PCField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@PCValue) END IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return WHILE @Work < @Rows AND @Status = 0 BEGIN SET @Work = @Work + 1 SELECT @Name = Name, @PKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Work IF ISNULL(@DBAdmin,0) <= 0 SET @Cash = @Bank SET @Wish = @Work WHILE @Wish > 1 BEGIN SET @Wish = @Wish - 1 SELECT @Same = Name, @ZKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Wish IF ISNULL(@DBAdmin,0) <= 0 SET @Cash = @Bank SET @Link = NULL IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @ZKey) SET @Link = @ZKey IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Same AND C.name = @PKey) SET @Link = @PKey IF @Link IS NOT NULL BEGIN SET @Loan = ' JOIN ' + @TPre + @Name + ' WITH (NOLOCK) ON ' + @TPre + @Same + '.' + @Link + ' = ' + @TPre + @Name + '.' + @Link SET @Bank = @Bank + @Loan SET @Cash = @Cash + @Loan IF @PCField IS NOT NULL AND @PCValue IS NOT NULL BEGIN IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @PCField) BEGIN SET @Loan = ' AND ' + @TPre + @Name + '.' + REPLACE(@PCField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@PCValue) SET @Bank = @Bank + @Loan SET @Cash = @Cash + @Loan END END WHILE @Wish > 1 BEGIN SET @Wish = @Wish - 1 SELECT @Same = Name, @ZKey = PKey FROM #Work WHERE Work = @Wish SET @Link = NULL IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @ZKey) SET @Link = @ZKey IF EXISTS (SELECT * FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Same AND C.name = @PKey) SET @Link = @PKey IF @Link IS NOT NULL BEGIN SET @Loan = ' AND ' + @TPre + @Same + '.' + @Link + ' = ' + @TPre + @Name + '.' + @Link SET @Bank = @Bank + @Loan SET @Cash = @Cash + @Loan END END END ELSE BEGIN IF @Wish = 1 UPDATE #Work SET Name = CHAR(45) WHERE Work = @Work END END UPDATE #Work SET Bank = @Cash WHERE Work = @Work END SET @Work = 1 SELECT @Name = Name, @PKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Work IF ISNULL(@DBAdmin,0) < 0 SET @Cash = @Bank SET @Task = 'SELECT DISTINCT ' + @DBAT + '.* FROM ' + REPLACE(REPLACE(@Cash,@Lock+CHAR(32),@DBAT+CHAR(32)),@Lock+CHAR(46),@DBAT+CHAR(46)) IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return WHILE @Work < @Rows AND @Status = 0 BEGIN SET @Work = @Work + 1 SELECT @Name = Name, @Cash = Bank FROM #Work WHERE Work = @Work IF ISNULL(@DBAdmin,0) < 0 SET @Cash = @Bank SET @Task = 'SELECT DISTINCT ' + @TPre + @Name + '.* FROM ' + REPLACE(REPLACE(@Cash,@Lock+CHAR(32),@DBAT+CHAR(32)),@Lock+CHAR(46),@DBAT+CHAR(46)) IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return END SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAT + CHAR(39) + ') DROP TABLE ' + @DBAT EXECUTE (@Task) DROP TABLE #Work SET NOCOUNT OFF RETURN (@Status) GO --------------------------------------------------------------------