A temp table created in a stored procedure is deleted when stored procedure finishes. If you create a temp table in the calling sproc than you can access it in the called sproc.


>The following dynamic SQL script works for creating a table on the fly but if I change the select @tmpTblNm = 'tmpABC' to select @tmpTblNm = '#tmpABC'
>it will not create the temp table. Can anyone help on creating a temp table dynamiclly?

>
>declare @tmpTblNm varchar(40),
>@str varchar(1000)
>
>select @tmpTblNm = 'tmpABC'
>select @str = ''
>
>-- Create a temp table to hold the current page of data
>-- Add an ID column to track the current row
>select @str = 'Create Table '+ @tmpTblNm +
>' ( row_id int IDENTITY PRIMARY KEY,
>customerID int,
>customerName varchar(40),
>address1 varchar(40),
>city varchar(25) )'
>
>exec (@str)