Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Variable Dynamically


Create Variable Dynamically

Author
Message
ravi1.shah
ravi1.shah
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 67
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.
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Administrators
Points: 49126 Visits: 18997
Not really. Perhaps if you give more info about the proc or post some code, we will have other ideas.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
The Dixie Flatline
The Dixie Flatline
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4418 Visits: 6900
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
ravi1.shah
ravi1.shah
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 67
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
Attachments
AdminSP.txt (34 views, 7.00 KB)
The Dixie Flatline
The Dixie Flatline
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4418 Visits: 6900
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
Matt Miller (4)
Matt Miller (4)
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9807 Visits: 18457
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search