Thanks for the Response.

1. The number of rows stored in Temp table is many.

2. I agree that required data is present in temp table but i need to compare each row in Temp Table with the other tables. So it is require to store those values first in dynamic varaible ( as i am not aware the number and type of variable) and then validate those values in other table and thereafter make some decision.

3. Reason for creating Store Procedure : To upload different Excel data into the database. I am trying to create a generic stored procedure to upload Excel data into the database. So my Table keeps on changing as also column name. I hope , know you have clear picture reason behind my stored procedure.

I am attaching and pasting piece of code which is in progress.......

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

--QQSM_NSS_SP_Admin_Import_MOSS_Data

ALTER PROCEDURE dbo.QQSM_NSS_SP_Admin_Import_MOSS_Data

@XMLData varchar(8000) = '

',--NULL,

@XMLSchema NTEXT = '

',--NULL,

@XMLDynamicTable NTEXT='

'

--

-- Add the parameters for the stored procedure here

AS

BEGIN

--Create temp table

CREATE TABLE #TempXmlData

(XmlDataID INT Identity(1,1) NOT NULL)

-- XML Doc

DECLARE @iDocData INT,@iDocSchema INT,@iDocTable INT

-- Table for Comma Separated Values

DECLARE @tblValues TABLE

(ValueID INT IDENTITY(1,1),

Value VARCHAR(100))

-- Counters

DECLARE @iCount INT,@iCounter INT

DECLARE @ParmDefinition NVARCHAR(500)

-- XmlData fields

Declare @Action varchar(15),

@KeyColumn varchar(30),

@sqlquery nvarchar(4000),

@KeyColumnValue INT,

--@KeyColumnValueOUT INT,

@DataTableName varchar(50),

@InsertCols varchar(1000),

@UpdateCols varchar(1000),

@ValidateCols varchar(1000)

DEclare @CreateQuery varchar(4000)

DEclare @CreateVariable varchar(4000)

declare @mytable varchar(30)

select @mytable = '#dtXmlData'

EXEC sp_xml_preparedocument @iDocData output, @XMLData

EXEC sp_xml_preparedocument @iDocSchema output, @XMLSchema

EXEC sp_xml_preparedocument @iDocTable output, @XMLDynamicTable

select @CreateQuery = Query

FROM OpenXML(@iDocTable,'/NewDataSet/dtDynamicTable',1)

WITH (Query nvarchar(4000) 'Query')

-- Alter temp table structure

--print @CreateQuery

execute (@CreateQuery)

--print '@CreateVariable: ' + @CreateVariable

--execute (@CreateVariable)

--print '@CreateVariable: ' + @CreateVariable

--execute ('set @iDocData12=1')

-- Get Xml Data into temp table

INSERT INTO #TempXmlData

select *

FROM OpenXML(@iDocData,'/NewDataSet/dtSLAParams',2)

With #TempXmlData

select * from #TempXmlData

-- Get Schema data into temp table

select * INTO #TempXMLSchema

FROM OpenXML(@iDocSchema,'/NewDataSet/Table_Details',2)

With (TableName varchar(50) 'TableName',

InsertCol varchar(500) 'InsertCol',

UpdateCol varchar(500) 'UpdateCol',

ValidateCol varchar(500) 'ValidateCol',

DeleteCol varchar(500) 'DeleteCol'

)

select * from #TempXMLSchema

-- Get Mapping and validation data into temp table

select * INTO #TempColMapping

FROM OpenXML(@iDocSchema,'/NewDataSet/Mapping_table',2)

With (ColumnName varchar(50) 'ColumnName',

MappingColumnName varchar(500) 'MappingColumnName',

ValidateTableName varchar(500) 'ValidateTableName'

)

select * from #TempColMapping

-- Loop through Xml Data table

select @iCount = count(XmlDataID) from #TempXmlData

set @iCounter = 1

While @iCount >= @iCounter

Begin --While @iCount <= @iCounter

--Check for action

select @Action = [Action] from #TempXmlData where XmlDataID = @iCounter

--get unique Column name for the identity column

select @KeyColumn = [DeleteCol] from #TempXMLSchema

select @DataTableName = [TableName] from #TempXMLSchema

print @KeyColumn

print @DataTableName

-- get Value for the unique column name

set @sqlquery = N'Select @KeyColumnValueOUT = ' + @KeyColumn + ' From #TempXmlData where XmlDataID = @iCounter'

SET @ParmDefinition = N'@iCounter INT,@KeyColumnValueOUT INT OUTPUT'

EXECUTE sp_executesql @sqlquery,@ParmDefinition,@iCounter=@iCounter,@KeyColumnValueOUT=@KeyColumnValue OUTPUT

SELECT @KeyColumnValue

-- Start Validation for

SELECT @ValidateCols = ValidateCol from #TempXMLSchema

IF @ValidateCols IS NOT NULL

BEGIN --@ValidateCols IS NOT NULL

Declare @iNoOfCols INT,

@iValidateCounter INT,

@ValidateCol VARCHAR(100), -- Validate Column Name

@ValidateColValue VARCHAR(100), -- Validate Column Value

@ValidateMappingCol VARCHAR(100), -- Validate Mapping Column Name

@ValidateTableName VARCHAR(100)

--DBCC CHECKIDENT (@tblValues, RESEED, 1)

-- Insert Values into values table

INSERT INTO @tblValues

SELECT OrderId from QQSM_SPLitLIST (@ValidateCols)

-- Get Count

Select @iNoOfCols = Max(ISNULL(ValueID,0)) from @tblValues

select @iValidateCounter = Min(ISNULL(ValueID,1)) from @tblValues

--Loop through each validate columns

While @iNoOfCols >= @iValidateCounter

Begin --While @iCount >= @iCounter

--Get the validate column name

select @ValidateCol = Value from @tblValues where ValueID = @iValidateCounter

-- Get the mapping Column name if exists

Select @ValidateMappingCol = MappingColumnName,@ValidateTableName = ValidateTableName from #TempColMapping where ColumnName = @ValidateCol

--If Mapping column doest exists, consider the validate col as mapping column

select @ValidateMappingCol = ISNULL(@ValidateMappingCol,@ValidateCol)

select @ValidateMappingCol,@ValidateTableName

--Get the value to validate from XMLData

set @sqlquery = N'Select @ValidateColValueOUT = ' + @ValidateCol + ' From #TempXmlData where XmlDataID = @iCounter'

SET @ParmDefinition = N'@iCounter INT,@ValColumnValueOUT VARCHAR(100) OUTPUT'

EXECUTE sp_executesql @sqlquery,@ParmDefinition,@iCounter=@iCounter,@ValidateColValueOUT=@ValidateColValue OUTPUT

SELECT @ValidateColValue

select dbo.QQSM_NSS_fn_ValidateData('Country','CountryID','tblLUP_Country','Canada')

-- Incremente counter

set @iValidateCounter = @iValidateCounter + 1

end --While @iCount >= @iCounter

delete from @tblValues

END --@ValidateCols IS NOT NULL

-- If Action is Add

If @Action = 'Add'

Begin --@Action = 'Add'

select 'Add'

End --@Action = 'Add'

--Increment counter

set @iCounter = @iCounter + 1

end --While @iCount <= @iCounter

--clean up activity

--drop table TBL_XmlData

EXEC sp_xml_removedocument @iDocData

EXEC sp_xml_removedocument @iDocSchema

EXEC sp_xml_removedocument @iDocTable

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Thanks,

Ravi