Creating a temporary table with a variable name

[mbabcock]

I have a VFP9 app that runs a SQL Server stored procedure. That stored procedure creates a temporary table (among other things). Right now, I've hardcoded it as ##TempTable, but I want to pass a unique name from my VFP app to the SQL Server proc. That way, I avoid problems by other concurrent users who might wish to run the same stored proc (with differing results stored in the temp table).

I can't use just #TempTable (only one # sign) because there are a few procedures that run that reference the stored proc.

I tried passing a string parm to a simple CreateTempTable SQL stored procedure but that only created a table with the name as my parm! (e.g., ##@tcTable)

Ideas on how to address this so that multi-user collisions don't happen?

tia!
--Michael

 

[SQLDenis]

you can use the same local temp table without a problem

open up query analyzer
open 2 query windows
create the same temp table and you will see that there is no problem

first window
create table #temp (id int)

select * from tempdb..sysobjects
#temp_______________________________________________________________________________________________________________000000000192

second window
create table #temp (id int)

select * from tempdb..sysobjects
#temp_______________________________________________________________________________________________________________00000000036E


as you can see SQL server appends a long string with a bunch of underscores and a a code at the end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TysonLPrice]

You might help your application by using a table variable instead of a temp table if the application would support it. That way you avoid the overhead of the logging that occurs with temp tables:

Declare @TempTable Table(definitions)

It depends on what you are doing with the #TempTable.

 

[mbabcock]

I can't use the suggestion from SQLDennis because more than one stored proc runs referencing that temp table. I basically am putting some tables together temporary from VFP data that I want to crunch on the server rather than bringing down all the data to the client and doing it in VFP. This approach also allows me to make it easier to migrate the front-end to a different tool than VFP when the time comes. (DotNet seems to be in the plans, so doing this now will serve both the current and future front-ends.)

Where would I put the DECLARE @TempTable Table(iid int, cname char(15), etc.) ???

tia,
--Michael

 

[TysonLPrice]

Define it just like any other variable. I always define them all at the top of the SP but that's just my style. It just needs defined before you use it. I've never passed one between SPs. That might be a bad Idea...

 

[mbabcock]

I guess I should look at a way to include everything in one SP I guess so as to avoid the problem. The thing is, I was hoping to get re-use out of some of the queries; hence why I had some of them separate.

 

[mbabcock]

Perhaps I'm approaching this from the wrong angle. Here's my need: I need to send any number of single values to SQL Server so that several queries can act upon those values. I was originally thinking a temp table but if there's a way I could pass a list of values to a stored procedure, that'd suffice. Generally, I have queries that use the list of values passed like so:

select *
from MySQLTable a1
inner join SomeOtherRelatedTables b1
on a1.key = b1.key
where a1.cKeyField in (list of values passed from client app)

Is there a way to pass this list of values and have it easily usable inside an SQL statement as opposed to using temp tables like I tried initially? The problem, to repeat, is that multiple users might kick off the process with different criteria, so they can't use the same temp tables at the same time.

Any help appreciated...thanks!
--Michael

 

[TysonLPrice]

I know you can from VB. If you don't need all the fields in the table drop the * and just select what you need.

 

[mbabcock]

But what's the logic behind parsing the incoming parameter?

For example, if I passed the parameter to be as follows:

@cMyParameter = '123,456,789'

...I can't just reference that in my IN clause:

CREATE PROCEDURE dbo.mjbtest (@cList)
AS
select * from MyTable where iKeyID in (@cList)

...it throws an error when checking syntax saying "must declare the variable '@cList'

How do I create the SQL statement on the fly at the SQL Server side and then execute it? In VFP, I can create commands in a variable and then automatically run them with commands like @lcCmd or EXECSCRIPT(lcCmd).

I'd like to keep this at the SQL Server stored procedure side so that maintenance is easier, but if this can't happen, I'll have to do all the manipulation on the VFP side I guess.

Thanks,
--Michael

 

[TysonLPrice]

Try:

CREATE PROCEDURE dbo.mjbtest (@cList VarChar(nnn))
AS
select * from MyTable where iKeyID in (@cList)

 

[mbabcock]

DOH! Yes of course...I forgot to add the input type. Ok...did that, BUT, it doesn't like the difference in types now:

"Syntax error converting the varchar value '1248,1249' to a column of data type int."

hang in there with me brother, we're almost there! ;-)