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
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
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
A lot of problems with what you are trying that I just don’t think this particular approach is going to work (I think the goal is interesting):
returns @result table (reportID bigint) – this is basically schema-bound. You can’t alter the schema of the returned table – I see later you are going to attempt to “ALTER” this variable – that’s just not going to happen.
Even if you could, this part:
select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) ; exec sp_executesql @alterTableStr;
isn’t going to affect the @result in the outer part – these dynamic SQL parts have their own scope and can’t get to the variables in the calling part this way
And then it really comes down to the error you are currently getting – which is sp_executesql can’t be run inside a function anyway.
Anyway, to aid you a little in making progress, may I suggest you read the following two articles which are are tangentially related (and I have used recently) regarding parsing CSV and JSON data. Both use his hierarchy idea (an unpivoted name/value thing) and repivoting and you might find some useful techniques there:
If you can use the CLR, this article which was published might also be useful: