InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 00:28:18 GMT20http://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxI also don't have the time to plow through everything you've posted. That being said - if you're POSITIVE this is the very best way to do this, you may consider dynamically creating the stored procedure itself rather than just a dynamic insert. The code might actually turn out to be simpler to code that way.Mon, 29 Dec 2008 10:19:40 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxI may have to apologize at this point. For the last 20-30 minutes, I've been browsing through your extremely procedural code. I can only say that I would try to redesign it entirely to do validation based on columns, not variables, flag the appropriate action in yet another column, then execute the appropriate action. Unfortunately, I can't spare the time to fully understand and rewrite it. To keep you in the procedural code that you are apparently most comfortable with, my answer at this point has to be that I know of no way to dynamically create variables. What you CAN do is dynamically write the code to create user-defined functions and stored procedures, then execute them. This is going to run VERY slowly in production and creates all sorts of issues if you plan to do this as an application that multiple users can run. I don't advocate this, but it's the only suggestion I can think of to get you around the roadblock in the path you are on.Mon, 29 Dec 2008 09:30:01 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxThanks 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 GOSET ANSI_NULLS ON GO--QQSM_NSS_SP_Admin_Import_MOSS_DataALTER PROCEDURE dbo.QQSM_NSS_SP_Admin_Import_MOSS_Data [code]@XMLData varchar(8000) = ' ',--NULL, @XMLSchema NTEXT = ' ',--NULL, @XMLDynamicTable NTEXT=' ' -- [/code] -- Add the parameters for the stored procedure hereASBEGIN --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 = QueryFROM OpenXML(@iDocTable,'/NewDataSet/dtDynamicTable',1)WITH (Query nvarchar(4000) 'Query')-- Alter temp table structure--print @CreateQueryexecute (@CreateQuery)--print '@CreateVariable: ' + @CreateVariable--execute (@CreateVariable)--print '@CreateVariable: ' + @CreateVariable--execute ('set @iDocData12=1')-- Get Xml Data into temp tableINSERT INTO #TempXmlDataselect * FROM OpenXML(@iDocData,'/NewDataSet/dtSLAParams',2) With #TempXmlDataselect * from #TempXmlData-- Get Schema data into temp tableselect * INTO #TempXMLSchemaFROM 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 tableselect * INTO #TempColMappingFROM 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 tableselect @iCount = count(XmlDataID) from #TempXmlDataset @iCounter = 1While @iCount >= @iCounterBegin --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 + 1end --While @iCount <= @iCounter--clean up activity--drop table TBL_XmlDataEXEC sp_xml_removedocument @iDocData EXEC sp_xml_removedocument @iDocSchema EXEC sp_xml_removedocument @iDocTable ENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOThanks,RaviMon, 29 Dec 2008 01:58:30 GMTravi1.shahhttp://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxSince you are doing this in dynamic SQL, you should also be capable of writing the dynamic SQL to create a variable for each column your are selecting from, and to populate them. Off the top of my head, I can see this leading to multiple dynamic SQL executions with you storing variables in a table to pass between steps. This seems overly complex, so let me ask a few questions:1.) First question is [i]why[/i] do you feel you need to do this? What are you going to do with those variables once you have them? You already have the values available in the temp table. There is probably a simpler way to achieve your ultimate goal.2.) Does the temp table contain only one row? 3.) Could you please post up a sample of both the query and some data?Fri, 26 Dec 2008 09:35:42 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxNot really. Perhaps if you give more info about the proc or post some code, we will have other ideas.Fri, 26 Dec 2008 09:23:58 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic625916-338-1.aspxThere are any alternative to declare a variable dynamicall with the stored procedure. Detail Problem Description--------------------------1. I am creating a table with in the stored procedure dynamically using Execute command and storing it in Temporary Table.2. Now I want those values in Temporary Table to be stored in some variables but I donot know how in advance the total number of varaible my dynamic table as my dynamic table will keep on vary.Sp it is not possible to declare during stored procedure creation. Need some way to create it dynamically.Please help. Thanks in advance.Fri, 26 Dec 2008 09:10:59 GMTravi1.shah