December 26, 2008 at 9:10 am
There 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.
December 26, 2008 at 9:23 am
Not really. Perhaps if you give more info about the proc or post some code, we will have other ideas.
December 26, 2008 at 9:35 am
Since 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 why 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?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 29, 2008 at 1:58 am
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
December 29, 2008 at 9:30 am
I 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.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 29, 2008 at 10:19 am
I 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.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply