Here it is my applying for the dynamic statements:
use Test2000
go
IF OBJECT_ID (N'dbo.WHATTOUSE', N'U') IS NOT NULL
DROP TABLE dbo.WHATTOUSE
GO
CREATE TABLE [dbo].WHATTOUSE (
[ID] Int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,A1 Bit
,B1 Bit
,C1 Bit
,D1 Bit
) ON [PRIMARY]
GO
Insert into [dbo].WHATTOUSE (A1,B1,C1,D1) values (1,1,1,1)
IF OBJECT_ID (N'dbo.DOCUMENTS', N'U') IS NOT NULL
DROP TABLE dbo.DOCUMENTS
GO
CREATE TABLE [dbo].DOCUMENTS (
[ID] Int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,AA1 Varchar (24)
,BB1 Varchar (24)
,CC1 Varchar (24)
,DD1 Varchar (24)
,BATCH_ID Int NOT NULL CONSTRAINT [DF_DOCUMENTS_BATCH_ID] DEFAULT 0
) ON [PRIMARY]
GO
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1,BATCH_ID) values ('my','td','fm','rr',999999)
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','ff','rn')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','fm','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1) values ('my','td','fm','rr')
Insert into [dbo].DOCUMENTS (AA1,BB1,CC1,DD1,BATCH_ID) values ('my','td','fm','rr',999999)
GO
IF OBJECT_ID (N'dbo.BATCHES', N'U') IS NOT NULL
DROP TABLE dbo.BATCHES
GO
CREATE TABLE [dbo].BATCHES (
[BATCH_ID] Int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,AAP1 Varchar (24)
,BBP1 Varchar (24)
,CCP1 Varchar (24)
,DDP1 Varchar (24)
,DT [DateTime] NOT NULL CONSTRAINT [DF_BATCHES_DT] DEFAULT (GETDATE() )
) ON [PRIMARY]
GO
IF OBJECT_ID ( 'dbo.BATCH_CREATION_01', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.BATCH_CREATION_01
GO
CREATE PROCEDURE [dbo].BATCH_CREATION_01
AS
SET NOCOUNT ON
if object_id('tempdb..##TMPDOCTOP') is not null
BEGIN
RAISERROR(N'Previously batch creation process has not finished correctly. Pleas inform support stuff...',16,1)
RETURN 99;
END
if object_id('tempdb..##TMPDOC') is not null
BEGIN
RAISERROR(N'Previously batch creation process has not finished correctly. Pleas inform support stuff...',16,1)
RETURN 99;
END
DECLARE @A1 bit, @B1 bit, @C1 bit, @D1 bit
SELECT @A1=A1, @B1=B1, @C1 = C1, @D1 = D1 FROM dbo.WHATTOUSE where ID=1
DECLARE @SQL Varchar(8000)
DECLARE @LIST_FOR_INSERT Varchar(8000)
DECLARE @SQLINSERT Varchar(8000)
DECLARE @SQLUPDATE NVarchar(4000)
DECLARE @SQLUPD_FULL NVarchar(4000)
SET @SQL=''
SET @LIST_FOR_INSERT=''
SET @SQLUPDATE=''
SET @SQLUPD_FULL=''
IF @A1>0
BEGIN
SET @SQL=@SQL+'AA1,'
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+'AA1,'
SET @SQLUPDATE=@SQLUPDATE+'DD.AA1=NN.AA1 AND '
END
ELSE
BEGIN
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+''''','
END
IF @B1>0
BEGIN
SET @SQL=@SQL+'BB1,'
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+'BB1,'
SET @SQLUPDATE=@SQLUPDATE+'DD.BB1=NN.BB1 AND '
END
ELSE
BEGIN
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+''''','
END
IF @C1>0
BEGIN
SET @SQL=@SQL+'CC1,'
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+'CC1,'
SET @SQLUPDATE=@SQLUPDATE+'DD.CC1=NN.CC1 AND '
END
ELSE
BEGIN
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+''''','
END
IF @D1>0
BEGIN
SET @SQL=@SQL+'DD1,'
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+'DD1,'
SET @SQLUPDATE=@SQLUPDATE+'DD.DD1=NN.DD1 AND '
END
ELSE
BEGIN
SET @LIST_FOR_INSERT=@LIST_FOR_INSERT+''''','
END
SET @SQL=SUBSTRING(@SQL,1,LEN(@SQL)-1)
SET @LIST_FOR_INSERT=SUBSTRING(@LIST_FOR_INSERT,1,LEN(@LIST_FOR_INSERT)-1)
SET @SQLUPDATE=SUBSTRING(@SQLUPDATE,1,LEN(@SQLUPDATE)-4)
SET @SQLINSERT='INSERT ##TMPDOC (AA1,BB1,CC1,DD1) '+char(13) + char(10)
+' SELECT '+@LIST_FOR_INSERT+char(13) + char(10)+' FROM ##TMPDOCTOP '+char(13) + char(10)
SET @SQL='SELECT DISTINCT '+@SQL
SET @SQL=@SQL +char(13) + char(10)+' INTO ##TMPDOCTOP '
SET @SQL=@SQL +char(13) + char(10)+' FROM dbo.DOCUMENTS WHERE BATCH_ID=0'
BEGIN TRANSACTION TRN_BATCH_CREATION_01
CREATE TABLE ##TMPDOC (
[REC_ID] Int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,AA1 Varchar (24)
,BB1 Varchar (24)
,CC1 Varchar (24)
,DD1 Varchar (24)
)
exec (@SQL)
exec (@SQLINSERT)
DECLARE @ParmDefinition nvarchar(500);
SET @SQLUPD_FULL=N'
UPDATE [dbo].DOCUMENTS SET BATCH_ID = @NEWID WHERE
ID IN (SELECT ID FROM [dbo].DOCUMENTS AS DD INNER JOIN ##TMPDOC AS NN
ON '+@SQLUPDATE+'
WHERE DD.BATCH_ID=0 AND NN.REC_ID=@CURRENTROW)'
SET @ParmDefinition = N'@NEWID int,@CURRENTROW int';
DECLARE @NEWROWS Int, @CURRENTROW Int, @NEWID Int
SET @CURRENTROW=1
SELECT @NEWROWS=COUNT(*) FROM ##TMPDOC
WHILE @CURRENTROW<=@NEWROWS
BEGIN
INSERT dbo.BATCHES (AAP1,BBP1,CCP1,DDP1)
SELECT AA1,BB1,CC1,DD1 FROM ##TMPDOC WHERE REC_ID=@CURRENTROW
SET @NEWID=@@IDENTITY
--print @SQLUPD_FULL
EXECUTE sp_executesql @SQLUPD_FULL, @ParmDefinition,
@NEWID, @CURRENTROW;
--UPDATE [dbo].DOCUMENTS SET BATCH_ID = @NEWID WHERE
--ID IN (SELECT ID FROM [dbo].DOCUMENTS AS DD INNER JOIN ##TMPDOC AS NN
--ON DD.AA1=NN.AA1 AND DD.BB1=NN.BB1 AND DD.CC1=NN.CC1 AND DD.DD1=NN.DD1
--WHERE DD.BATCH_ID=0 AND NN.REC_ID=@CURRENTROW)
SET @CURRENTROW=@CURRENTROW+1
END
--SELECT * FROM ##TMPDOC
-- remove this from real SP
SELECT * FROM dbo.BATCHES
SELECT * FROM [dbo].DOCUMENTS
if object_id('tempdb..##TMPDOCTOP') is not null drop table ##TMPDOCTOP
if object_id('tempdb..##TMPDOC') is not null drop table ##TMPDOC
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION TRN_BATCH_CREATION_01
RAISERROR(N'An error occurred durring batch creation process.',16,1)
RETURN 99;
END
ELSE
BEGIN
COMMIT TRANSACTION TRN_BATCH_CREATION_01
RETURN 0
END
GO
exec (N'dbo.BATCH_CREATION_01')
GO