CODEWITHSUNDEEP
sql-server-2008
duckmike
duckmike

Reputation: 1036

Referencing a #temp table from an external process

Using SQL Server 2008, I have two stored procedures -

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

Note that there is one stored procedure calling another. The stored procedure being called creates a temp table and uses it within itself.

when I run -

exec dbo.SH_Export

I get this error message -

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

BUT I can run, without error -

exec dbo.SH_Export_data 63058

what causes that?

Upvotes: 1

Views: 3996

Answers (2)

Ben English
Ben English

Reputation: 3918

In addition to what Aaron Bertrand wrote. I'll add that you can also pass a table as a table valued parameter.

From 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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280330

You need a global temp table or a permanent table.

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

or

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

They both accomplish the same thing - a table that your external processes can access (a local #temp table is restricted to the scope of the caller). The difference is that the ##global temp table doesn't need to be explicitly dropped, but the permanent one does.

What this means, though, is that if two users call this stored procedure at the same time, one of them will generate an error (or completely undo what the other one started doing). If you can do all of the #temp table magic in dynamic SQL, you may consider adding the current session's spid or some other uniqueifier to the table name.

Upvotes: 3

Related Questions