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
