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!