USE [TEMPDB]
GO
/****** Object: StoredProcedure [dbo].[UPDATETABLE] Script Date: 11/25/2008 09:49:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.UPDATETABLE Script Date: 8/13/2008 4:05:35 PM ******/
CREATE PROCEDURE [dbo].[UPDATETABLE]
@totalTable INT,
@xmlStr NTEXT,
@empID VARCHAR(5)
AS
DECLARE @F_Name varchar(255),@F_Type varchar(1),@F_Name1 VARCHAR(255)
DECLARE @idoc INT
DECLARE @iteration INT
SET @iteration=1
EXEC SP_XML_PREPAREDOCUMENT @idoc OUTPUT, @xmlStr
DECLARE @root VARCHAR(255)
DECLARE @table VARCHAR(100)
DECLARE @cond VARCHAR(50)
DECLARE @fields VARCHAR(1000)
DECLARE @sqlQry VARCHAR(8000)
DECLARE @tableFrom VARCHAR(255)
SET @tableFrom='TEMP_TABLE'+@empID
WHILE @iteration<=@totalTable
BEGIN
SET @root='ROOT/TABLE'+CONVERT(VARCHAR(10),@iteration)
SELECT @table=TABLENAME, @fields=Fields,@cond=Condition FROM OPENXML (@idoc,@root)
WITH (TABLENAME VARCHAR(100), FIELDS VARCHAR(1000), CONDITION VARCHAR(1000))
SET @root=@root+'/FIELDS'
SET @sqlQry='CREATE TABLE '+@tableFrom+' (' + REPLACE(@fields,'|',' ') + ')'
EXEC (@sqlQry)
EXEC('
DECLARE @idoc INT
SET @idoc='+@idoc+'
INSERT INTO '+@tableFrom+'
SELECT * FROM OPENXML (@idoc,'''+@root+''') WITH '+@tableFrom)
SET @sqlQRY = DBO.UPDATEQRY(@fields,@table,@tableFrom) + ' ' + @cond
EXEC (@sqlQry)
EXEC('DROP TABLE '+@tableFrom)
Set @iteration=@iteration+1
END
EXEC SP_XML_REMOVEDOCUMENT @idoc
