T-SQL：当您具有时间戳列且无法对列名称进行硬编码时，将存储过程的结果放入临时表中

Question

对于我的SQL Server单元测试，当对"获取所有行"存储过程的结果进行计数时，我试图禁止返回所有行。为此，我试图将结果写入临时表。

我从指向当前服务器和数据库的链接服务器条目开始。

IF NOT EXISTS (SELECT * FROM sys.servers WHERE NAME = 'DB_LOCAL')
BEGIN
DECLARE @DBNAME VARCHAR(MAX) = (SELECT DB_NAME())
EXEC sp_addlinkedserver @server = 'DB_LOCAL',  @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername, @catalog=@DBNAME
END

这样，我就可以使用OPENQUERY动态创建一个具有存储过程输出结构的临时表，而不必对列进行硬编码。

IF OBJECT_ID('tempdb..#ut_results') IS NOT NULL 
DROP TABLE #ut_results
SELECT * 
INTO #ut_results
FROM OPENQUERY (DB_LOCAL, 'EXEC p_AnzLookup_get @id = 0')

我想使用这个临时表来执行我存储的proc，我不能再次使用OPENQUERY，因为我需要指定变量作为测试的一部分。此外，单元测试在事务中，这样做会产生锁定问题。一旦我有了结构，我就这样做。我不能在没有时间戳列的情况下指定列名，我很感激这一点，因为它们可以由第三方更改。

TRUNCATE TABLE #ut_results
INSERT INTO #ut_results 
EXEC p_AnzLookup_get @id = @record_id

此插入操作失败，因为存储过程返回了一个时间戳列。

Msg 273，级别16，状态1，第2行
无法在时间戳列中插入显式值。对列列表使用INSERT可以排除时间戳列，或者在时间戳列中插入DEFAULT。

由于此错误，我无法更改临时表中的时间戳列。

ALTER TABLE #ut_results 
ALTER COLUMN TStamp BINARY(8)

Msg 4928，Level 16，State 1，Line 5
无法更改列"TStamp"，因为它是"timestamp"。

我无法删除并重新创建临时表中的时间戳列，因为它会更改列顺序。

ALTER TABLE #ut_results DROP COLUMN TStamp 
ALTER TABLE #ut_results ADD TStamp BINARY(8)

当数据插入错误的列时，会导致不同的错误：

消息257，级别16，状态3，过程p_AnzLookup_get，第20行
不允许从数据类型datetime隐式转换为int。使用CONVERT函数运行此查询。

我不能更改这些单元测试的存储过程，也不能硬编码列名。我需要以一种既有弹性又能对我无法控制的变化做出反应的方式来写这篇文章。

这只是我为演示这个问题而提取的一个单元测试的一个子集。有没有想过我该如何度过这段艰难的时光？

Answer 1

我想我有一些可以工作的东西。使用OPENQUERY创建临时表，然后将其通过XML进行混合，将列名的csv列表转换为varchar。然后对此进行替换，将时间戳列强制转换为varbinary(8)。

使用上面的列列表，我可以将第一个临时表的结构选择到第二个临时表中，其中字段的顺序正确，时间戳列定义为varbinary(8)，可以接受时间戳数据。

这不太好看：

IF OBJECT_ID('tempdb..#ut_results') IS NOT NULL DROP TABLE #ut_results
IF OBJECT_ID('tempdb..#ut_results2') IS NOT NULL DROP TABLE #ut_results2
SELECT * INTO #ut_results2
FROM OPENQUERY ( DB_LOCAL ,'EXEC p_AnzLookup_get @id=-1' )

DECLARE @cols VARCHAR(MAX) = ''
SELECT @cols = Stuff(( select ',' + name from tempdb.sys.columns where object_id =
object_id('tempdb..#ut_results2')
FOR XML PATH(''), TYPE                       
).value('.', 'NVARCHAR(MAX)') 
, 1, 1, '')
SET @cols = REPLACE(@cols,'TStamp','CAST(TStamp AS VARBINARY(8)) AS TStamp')
DECLARE @SQl VARCHAR(MAX) = 'SELECT ' + @cols + ' INTO #ut_results FROM #ut_results2 WHERE 1=2'
EXEC (@SQl)
IF OBJECT_ID('tempdb..#ut_results2') IS NOT NULL DROP TABLE #ut_results2

编辑：这是一个小小的变化。在上面的文章中，我用动态SQL创建了最终的临时表，但之后无法访问。我把这张桌子改成了普通的桌子。

DECLARE @SQl VARCHAR(MAX) = 'SELECT ' + @cols + ' INTO ut_results FROM #ut_results2'
EXECUTE (@SQl)