如何將 baseType 的 sql_variant_property 恢復回 SQL Server 中的表變數

好的，我知道標題令人困惑，但它就在這裡。順便說一句，我正在使用 SQL Server 2008

我有一張桌子，我正試圖將其用作儲存容器。它有 3 列：

reportID integer, 
dataLabel varchar(50),
dataValue sql_variant 

我想使用它的方法是將 a 傳遞reportID給函式並讓函式返回一個準確鍵入的表變數。除了準確鍵入的部分外，我的流程已關閉。

下面是一個腳本，它將建構一個臨時表，插入一些值並將這些值轉換為我想要使用的表變數。問題是為每列返回的值類型是sql_variant，我無法弄清楚如何將它們轉換為它們的 baseType。我已經嘗試（不成功）使用sql_variant_property但無法弄清楚。

例如，在最終表格中，我希望該myDate列是一種dateTime類型而不是一種sql_variant類型。

是否有任何 SQL 大師可以將我推向正確的方向？

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

最終目標是 udf，因為儲存過程不能返回表變數。所以我用游標和一些動態sql嘗試了這個。我想我很接近但使用以下功能我得到了錯誤

只有函式和一些擴展儲存過程可以在函式內執行。嚴重性 16 狀態 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

您正在嘗試的很多問題我只是認為這種特殊方法行不通（我認為目標很有趣）：

returns @result table (reportID bigint)- 這基本上是模式綁定的。你不能改變返回表的模式——我稍後會看到你將嘗試“改變”這個變數——這是不會發生的。

即使可以，這部分：

select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) ;

exec sp_executesql @alterTableStr;

不會影響外部的 @result - 這些動態 SQL 部分有自己的範圍，並且無法以這種方式訪問呼叫部分中的變數

然後它真的歸結為您目前遇到的錯誤 - 這是 sp_executesql 無論如何都不能在函式內執行。

無論如何，為了幫助您取得一些進展，我建議您閱讀以下兩篇關於解析 CSV 和 JSON 數據的相關文章（我最近使用過）。兩者都使用他的層次結構思想（一個未透視的名稱/值的東西）和重新透視，您可能會在那裡找到一些有用的技術：

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/

如果您可以使用 CLR，那麼發表的這篇文章也可能有用：

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

引用自：https://dba.stackexchange.com/questions/29897