Create Temp Table with Variable Name?

[Stickarm]

How would I rewrite this code using a variable for the #temptbl I am creating?

CREATE PROC proctestSearch
@name varchar(61) =null
AS
Select *
into #temptbl
from Maintbl
where myname = @name

Thanks,
Phil

 

[Cagliostro]

try something like this:
dim @parm varchar(20)
dim @sql as varchar(100)
set @sql = 'Select * into ''' + @parm + 'from Maintbl where myname = ''' + @name + '''
exec @sql

John Fill

ivfmd@mail.md

 

[tlbroadbent]

You'll have to create a SQL statement and execute it using sp_executesql. This adds the additional difficulty that temporary tables created via a dynamic execution exist only in the context of that execution. Your table will need to be a global temporary table using ## rather than #.

CREATE PROC proctestSearch
@name varchar(61) =null
AS

declare @sql nvarchar(1000), @tname nvarchar(40)

set @tname='##temptbl'

Set @sql='Select * Into ' + @tname +
' From Maintbl Where myname = @name'

sp_executesql @sql,N'@name varchar(61)',@name
drop table ##temptbl

I have to ask why you want to input the temporary table name as a variable and add this complexity to the procedure. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Stickarm]

This DB was created in Access originally. I moved the tables to SQL 7.0, The main table consists of over 3million records and the users use it to search on one field only. I set it up where the I was creating a temp table in access based on the SQL query results then allowing the user to do a more advanced search if neccessary on that temp table. With this temp table the size of the access database contiuosly grows and I want all the querying done in SQL.
If you have a better suggestion, let me know, I am just learning SQL Server. There are a total of about 7 fields that can be searched on.