如何在不修改SQL Server存储过程的情况下添加一个WHERE条件？

Question

我如何在不修改SQL Server存储过程的情况下，对存储过程的结果添加一个where条件？我已经尝试了这种方法，但是遇到了错误，应该如何修正？

DECLARE @Colonnes TABLE 
                  (
                      NomColonne NVARCHAR(100),
                      DataType NVARCHAR(100)
                  )

INSERT INTO @Colonnes (NomColonne, DataType)
    SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'T_RecapElementTechnique'

DECLARE @query NVARCHAR(MAX) = 'DECLARE @ResultatsTemp TABLE ('

SELECT @query = @query + QUOTENAME(NomColonne) + ' ' + DataType + ',' 
FROM @Colonnes

SET @query = LEFT(@query, LEN(@query) - 1) + ')' 


EXEC sp_executesql @query

INSERT INTO @ResultatsTemp
EXEC adcvie.ChargerRecapElementsTechniques 15

-- Sélectionner les données de la table temporaire
SELECT * FROM @ResultatsTemp where EstVie=1

但是，我遇到了这样的错误：

Msg 1087, Niveau 15, État 2, Ligne 23
Must declare the table variable "@ResultatsTemp".
Msg 1087, Niveau 15, État 2, Ligne 27
Must declare the table variable "@ResultatsTemp";.

user4815162342 · Answer

动态SQL中创建的表变量和临时表在该动态SQL批处理结束时会被销毁。

因此，您可以选择以下任一方案：

将表变量的声明硬编码到存储过程主体中；
或者，将以下代码添加到动态SQL中：

...
set @query = @query + '
INSERT INTO @ResultatsTemp
EXEC adcvie.ChargerRecapElementsTechniques 15

-- 从临时表中选择数据
SELECT * FROM @ResultatsTemp WHERE EstVie=1
'

EXEC sp_executesql @query

加入到动态SQL中。

Ben Gottlieb · Answer

变量不会跨越executeSql的作用域。然而，通过使用“select...into #temptable”查询，实际上可以完全省去executeSql的使用。

所以您的代码看起来会是这样的：

select top 0 * 
into #ResultatsTemp
from T_RecapElementTechnique

insert into #ResultatsTemp
exec adcvie.ChargerRecapElementsTechniques 15

SELECT * FROM #ResultatsTemp where EstVie=1
Drop table #ResultatsTemp

此处，“select into”语句根据源表自动创建一个具有相同列和数据类型的新表，因此无需担心varchar等数据类型问题。

-----编辑-----
想到另一种利用变量实现的方法。

将声明部分移出动态SQL，在动态SQL中做如下操作：

SELECT @query = @query + 'alter table @ResultatsTemp 
add column ' + QUOTENAME(NomColonne) + ' ' + 
case when datatype like '%varchar' then datatype + '(max)' 
else datatype end + ';'
from @Colonnes

我现在无法亲自测试，但我认为这个方法应该可行。可能需要首先在声明表的时候添加一个虚拟列，然后在添加完所有其他列之后再将其删除。