从外部进程引用#temp表

Question

使用SQL Server 2008，我有两个存储过程-

create procedure [dbo].[SH_Export_data] (@unit varchar(5)) as
  declare @sqlquery varchar(max) = 'select check_dt from chk_data where unit = ' 
    + @unit
  IF object_id('tempdb..#TempTbl') IS NOT NULL
    DROP TABLE #TempTbl
  create TABLE #TempTbl (
    col1 varchar(max), col2 varchar(max)
  )
  insert #TempTbl(col1)
  exec(@sqlquery)

create procedure [dbo].[SH_Export] as
  DECLARE @unit varchar(5), @sql varchar(max) = '', @file_name = 'c:export.xls'
  DECLARE crsr CURSOR for
    select unit
    from communities
  OPEN crsr
  FETCH NEXT FROM crsr
  into @unit
  while @@FETCH_STATUS = 0 
  BEGIN
    set @sql = 'exec master..xp_cmdshell ''bcp "exec dbo.SH_Export_data ' +
      @unit + '" queryout "' + @file_name + '" -c -T "''
  END

请注意，有一个存储过程在调用另一个。被调用的存储过程会创建一个临时表并在自身中使用它。

当我运行时-

exec dbo.SH_Export

我收到这个错误消息-

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server] 
Invalid object name '#TempTbl'.

但我可以运行，没有错误-

exec dbo.SH_Export_data 63058

是什么原因造成的？

Answer 1

您需要一个全局临时表或一个永久表。

create TABLE ##TempTbl (
    col1 varchar(max), col2 varchar(max)
  )

或

create TABLE dbo.TempTbl (
    col1 varchar(max), col2 varchar(max)
  )

它们都完成了相同的事情——一个外部进程可以访问的表（一个本地#temp表被限制在调用者的范围内）。不同的是，##全局临时表不需要显式删除，但永久临时表需要。

不过，这意味着，如果两个用户同时调用此存储过程，其中一个用户将生成错误（或完全撤消另一个用户开始执行的操作）。如果您可以在动态SQL中完成所有的#temp表魔术，那么您可以考虑将当前会话的spid或其他一些uniquefier添加到表名中。

Answer 2

除了Aaron Bertrand写的。我将补充一点，您还可以将表作为表值参数传递。

来自MSDN：http://msdn.microsoft.com/en-us/library/bb510489.aspx

USE AdventureWorks2008R2;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO