Sql-server – How to restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008

I have a table that I am trying to use as a storage container. It has 3 columns:

reportID integer, 
dataLabel varchar(50),
dataValue sql_variant 

The way I want to use this is to pass a reportID to a function and have the function return a table variable that is accurately typed. I have the process down EXCEPT for the accurately typed part.

Below is a script that will build a temp table, insert in some values and the pivot the values to the table variable that I would want to use. The problem is that the values type returned for each column is sql_variant and I cannot figure out how to convert them to their baseType. I have tried (unsuccessfully) to use the sql_variant_property but can't figure it out.

For example, in the final table I would like the myDate column to be a dateTime type and not a sql_variant type.

Are there any SQL gurus who can push me in the right direction?

create table #rStorage
    (
      reportID  bigint not null
    , dataLabel varchar (50) null
    , dataValue sql_variant null
    )
go

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myInt', convert(int,5621))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydate', convert(smalldatetime,getdate()))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myvarchar', convert(varchar(200),'testing Varchar'))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydateNoTconverted', getDate())

select reportID
, dataLabel
, dataValue
into #tmp
from #rStorage

declare @QuestionList nvarchar(max)
, @qry nvarchar(max)

SELECT @QuestionList = STUFF(
 (SELECT ', ' + quotename(dataLabel)
 FROM #tmp
 GROUP BY dataLabel
 ORDER BY dataLabel
 FOR XML PATH(''))
 , 1, 2, '');

select @qry = '
 select *
 from #tmp

 pivot 
 (
 max(dataValue)
 for dataLabel in ('+@QuestionList+')
 ) as q
 '

print @qry

exec sp_executesql @qry;

drop table #tmp
drop table #rStorage

The eventual goal is a udf since a stored proc can't return a table variable. So I tried this with a cursor and some dynamic sql. I think I am close but with the below function I get the error

Only functions and some extended stored procedures can be executed
from within a function. Severity 16 State 2

Code:

if exists (select * from sysobjects where  name = N'fn_pivotReportStorage')
   drop function fn_pivotReportStorage
go

create function fn_pivotReportStorage 
 (@reportID bigint)
 returns @result table (reportID bigint)
as
begin

    declare @dataLabel sql_variant
    ,   @dataValue sql_variant
    ,   @dataType sql_variant

    ,   @alterTableStr nvarchar(max)

    declare thisCursor Cursor fast_forward for
       select   dataLabel
            ,   dataValue
            ,   dataType = case SQL_VARIANT_PROPERTY(dataValue,'BaseType') 
                        when 'varchar' then convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'BaseType'))+'('+convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'maxLength'))+')'
                        else SQL_VARIANT_PROPERTY(dataValue,'BaseType') end
        FROM reportStorage 
        where reportID = @reportID

    insert into @result (reportID) values (@reportID)

    open thisCursor

    fetch next from thisCursor into @dataLabel,@dataValue,@dataType 

    while @@fetch_status = 0
    begin
            select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) 
            exec sp_executesql @alterTableStr;
            select @alterTableStr = 'update @result set '+ convert(varchar(2000),@dataLabel)+' = '''  +convert(varchar(2000),@dataValue)+''''
            exec sp_executesql @alterTableStr;
     fetch next from thisCursor into @dataLabel,@dataValue,@dataType 
    end
    close thisCursor
    deallocate thisCursor

 return 
end
go