Take the 2-minute tour ×
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.

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
    
Please explain how 12 people look at this and 2 actually spend the time and effort to correct MSSQL to SQL server and the way that the word look in my question but not add anything constructive. Not that I don't appreciate a useful edit but edits for the sake of an edit or to up some sort of "rep" is not really helpful –  Lance Dec 6 '12 at 0:05
3  
There really isn't a whole lot of credit to be had by editing. No points for reputation. Maybe a badge at certain levels that really doesn't mean a whole lot... People edit the questions here because they care about the quality of this site and want to help it be useful. Maybe they want to help attract an answerer. They may not even know anything about the specific topic. But either way, berating the potential answerers of a longish question (not complaining, details are good) is a really horrible way to attract answers. You'll still get one I'm sure, but... –  Mike Walsh Dec 6 '12 at 2:39
    
And for one quick thought to help you. Don't have the time to delve into a proper answer so this is a comment.. There aren't any points or secret credits for upvotes on comments, btw. at least not that I know about... You have dynamic SQL in that function. That's why you are getting that error most likely.. –  Mike Walsh Dec 6 '12 at 3:40

1 Answer 1

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.