Getting a Temporary Table Returned from from Dynamic SQL in SQL Server 05, and parsing
- by gloomy.penguin
So I was requested to make a few things.... (it is Monday morning and for some reason this whole thing is turning out to be really hard for me to explain so I am just going to try and post a lot of my code; sorry)
First, I needed a table:
CREATE TABLE TICKET_INFORMATION (
TICKET_INFO_ID INT IDENTITY(1,1) NOT NULL,
TICKET_TYPE INT,
TARGET_ID INT,
TARGET_NAME VARCHAR(100),
INFORMATION VARCHAR(MAX),
TIME_STAMP DATETIME DEFAULT GETUTCDATE()
)
-- insert this row for testing...
INSERT INTO TICKET_INFORMATION (TICKET_TYPE, TARGET_ID, TARGET_NAME, INFORMATION) VALUES (1,1,'RT_ID','IF_ID,int=1&IF_ID,int=2&OTHER,varchar(10)=val,ue3&OTHER,varchar(10)=val,ue4')
The Information column holds data that needs to be parsed into a table. This is where I am having problems. In the resulting table, Target_Name needs to become a column that holds Target_ID as a value for each row in the resulting table.
The string that needs to be parsed is in this format:
@var_name1,@var_datatype1=@var_value1&@var_name2,@var_datatype2=@var_value2&@var_name3,@var_datatype3=@var_value3
And what I ultimately need as a result (in a table or table variable):
RT_ID IF_ID OTHER
1 1 val,ue3
1 2 val,ue3
1 1 val,ue4
1 2 val,ue4
And I need to be able to join on the result. Initially, I was just going to make this a function that returns a table variable but for some reason I can't figure out how to get it into an actual table variable. Whatever parses the string needs to be able to be used directly in queries so I don't think a stored procedure is really the right thing to be using.
This is the code that parses the Information string... it returns in a temporary table.
-- create/empty temp table for var_name, var_type and var_value fields
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
create table #temp (row int identity(1,1), var_name varchar(max), var_type varchar(30), var_value varchar(max))
-- just setting stuff up
declare @target_name varchar(max), @target_id varchar(max), @info varchar(max)
set @target_name = (select target_name from ticket_information where ticket_info_id = 1)
set @target_id = (select target_id from ticket_information where ticket_info_id = 1)
set @info = (select information from ticket_information where ticket_info_id = 1)
--print @info
-- some of these variables are re-used later
declare @col_type varchar(20), @query varchar(max), @select as varchar(max)
set @query = 'select ' + @target_id + ' as ' + @target_name + ' into #target; '
set @select = 'select * into ##global_temp from #target'
declare @var_name varchar(100), @var_type varchar(100), @var_value varchar(100)
declare @comma_pos int, @equal_pos int, @amp_pos int
set @comma_pos = 1
set @equal_pos = 1
set @amp_pos = 0
-- while loop to parse the string into a table
while @amp_pos < len(@info) begin
-- get new comma position
set @comma_pos = charindex(',',@info,@amp_pos+1)
-- get new equal position
set @equal_pos = charindex('=',@info,@amp_pos+1)
-- set stuff that is going into the table
set @var_name = substring(@info,@amp_pos+1,@comma_pos-@amp_pos-1)
set @var_type = substring(@info,@comma_pos+1,@equal_pos-@comma_pos-1)
-- get new ampersand position
set @amp_pos = charindex('&',@info,@amp_pos+1)
if @amp_pos=0 or @amp_pos<@equal_pos set @amp_pos = len(@info)+1
-- set last variable for insert into table
set @var_value = substring(@info,@equal_pos+1,@amp_pos-@equal_pos-1)
-- put stuff into the temp table
insert into #temp (var_name, var_type, var_value) values (@var_name, @var_type, @var_value)
-- is this a new field?
if ((select count(*) from #temp where var_name = (@var_name)) = 1) begin
set @query = @query + ' create table #' + @var_name + '_temp (' + @var_name + ' ' + @var_type + '); '
set @select = @select + ', #' + @var_name + '_temp '
end
set @query = @query + ' insert into #' + @var_name + '_temp values (''' + @var_value + '''); '
end
if OBJECT_ID('tempdb..##global_temp') is not null drop table ##global_temp
exec (@query + @select)
--select @query
--select @select
select * from ##global_temp
Okay. So, the result I want and need is now in ##global_temp. How do I put all of that into something that can be returned from a function (or something)? Or can I get something more useful returned from the exec statement? In the end, the results of the parsed string need to be in a table that can be joined on and used... Ideally this would have been a view but I guess it can't with all the processing that needs to be done on that information string.
Ideas?
Thanks!
