T-SQL Recipes之 Table Variables and Temporary Tables
Problem
许多时候, 我们想要Table Variables在动态SQL中执行,但现实是很骨感的。比如这个示例:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE @sql_command NVARCHAR(MAX); DECLARE @parameter_list NVARCHAR(MAX); DECLARE @last_names TABLE ( last_name NVARCHAR(50) ); SELECT @sql_command = ' SELECT DISTINCT FirstName FROM Person.Person WHERE LastName IN (SELECT last_name FROM @last_names)' EXEC sp_executesql @sql_command;
有人看到这里,也许第一直觉就会问,为什么不在动态SQL中申明@last_names,其实然并卵。
这条路走不通,那我们换条路吧,比如Type Table:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TYPE last_name_table AS TABLE (last_name NVARCHAR(50)); GO --DROP TYPE last_name_table DECLARE @sql_command NVARCHAR(MAX); DECLARE @parameter_list NVARCHAR(MAX); DECLARE @first_name_calling_sql NVARCHAR(50) = 'Edward'; DECLARE @last_names AS last_name_table; SELECT @sql_command = ' INSERT INTO @last_names ( last_name ) SELECT LastName FROM Person.Person WHERE FirstName = @first_name_calling_sql; SELECT DISTINCT FirstName FROM Person.Person WHERE LastName IN (SELECT last_name FROM @last_names) ' SELECT @parameter_list = '@first_name_calling_sql NVARCHAR(50), @last_names last_name_table READONLY' EXEC sp_executesql @sql_command, @parameter_list, @first_name_calling_sql,@last_names;
如果把Insert语句拿出来,当然是可以执行的,不过有些业务就是要动态处理,摊手中……
Solution
这时候该我们的临时表登场了。先看示例:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE @sql_command NVARCHAR(MAX); DECLARE @parameter_list NVARCHAR(MAX); IF OBJECT_ID('tempdb..#last_names') IS NOT NULL BEGIN DROP TABLE #last_names; END; CREATE TABLE #last_names ( last_name NVARCHAR(50) ); INSERT INTO #last_names ( last_name ) SELECT 'Thomas' SELECT @sql_command = ' INSERT INTO #last_names (last_name) SELECT ''Smith''; SELECT DISTINCT FirstName FROM Person.Person WHERE LastName IN (SELECT last_name FROM #last_names); ' EXEC sp_executesql @sql_command; SELECT * FROM #last_names; IF OBJECT_ID('tempdb..#last_names') IS NOT NULL BEGIN DROP TABLE #last_names; END;
从运行结果判定可以得出结论:临时表不仅可以在动态SQL中访问,还可以进行写操作。当然另外还有一种用永久表做临时存储的,这里就不提供示例了。
最后送一个小彩蛋,假设业务场景,我们传入参数是BusinessEntityId的序列,以逗号分隔。然后动态SQL执行,获取想要的person。这时候应该怎么做呢?