>To all,
>
>I'm writing a series of stored procedures in SQL Server 2000. My intent is for the procedures to create one or more local temp tables. The tables' name will be dynamically created and the dynamically created table name will be passed back through an output parameter.
>
>Issue one: What's the best way to create a unique string to be used as a temp table name?
>
>Issue two: How can I get SQL Server to accept the temp table name in a query?
>
>For example:
>
>DECLARE @cMyTempTable VarChar(25)
>SET @cMyTempTable = [unique string]
>
>SELECT *
>INTO #@cMyTempTable
>FROM dbo.MySourceTable
>

The only way you might be able to do this is via a dynamic SQL statement. (see sp_executesql in the BOL). That's about as close to VFP macro substitution as you can get in SQL. Something like this:

DECLARE @cMyTempTable VarChar(25)
SET @cMyTempTable = [unique string]

DECLARE @String NVARCHAR(1000)
set @String = N!'SELECT * INTO #' + @cMyTempTable + 
'FROM dbo.MySourceTable'

sp_executesql @string

I didn't test this so the usual disclaimers apply.

However - and this is a big warning - local temporary tables only exists as long as the session that creates them is alive. So, if you pass this temp table output parameter to, say, a client application, and sever the connection, you're hosed.