Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
up vote 4 down vote accepted

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),@dataType) ;

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:

http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-csv-comma-delimited-of-errors/

http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

If you can use the CLR, this article which was published might also be useful:

http://www.sqlservercentral.com/articles/.Net/94922/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.