Creating Unique Temp Table Name and Passing From One SP to another as a Parameter

I am fairly new to SQL Server, currently using Developer 14.  I have written some SP's but have not encountered what I'd like to do now.  I want to create a temp table that can be update by multiple SP's.

My understanding is that for this temp table to be available to other SPROCS it would have to have a ## prefix rather than #.

Since there is a possibility that multiple users might be executing this same logic at the same time, In the main SPROC I'd like to create the ## temp table with a unique name and then pass the name to the any other SPROCS that might have to act on the table.  My first obstacle is creating the unique name.  Is there a Rand function in SQL?

1. How would I go about creating a temp table with a unique name?

tempName = '##Multi' + Rand()

Having created the unique name what would be the syntax to create the new temp table definition?

Create Table tempname  InterestedPartyTypeID int,
                                   InterestedPartyType   nvarchar(30),
                                   Muni                  int

2. Now that I have a unique name and created a table using that name, in what form would I need to pass the table name to a  SPROC executed form the main routine?  Would it be as simple as passing the name as a nvarchar field (tempname)?

Execute SProc2 tempname

If so how would the 'called' SP reference the file?

Select * from passedTempTableName  wouldn't seem to work very well.

Thanks for any help.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Before I start with the explanations Can you tell me your requirement what you want to achieve? Please see if below helps-

>>My understanding is that for this temp table to be available to other SPROCS it would have to have a ## prefix rather than #.
No. The ##(Global Temp Table) will be available to all the sessions(you can say your sprocs) till the time the session who has created is available.

>> Is there a Rand function in SQL?
Yes.
SELECT RAND() 

Open in new window

But it will give Floating data values which will not help you. Instead we can use NEWID(). E.g.
SELECT CONCAT('[',REPLACE(NEWID(),'-',''),']') 

Open in new window


>>How to create unique global Temp table
We can use NEWID() For this-
E.g.
DECLARE @TName as VARCHAR(200) = CONCAT('[##',REPLACE(NEWID(),'-',''),']')
EXEC ( ' Create Table ' + @TName + ' ( InterestedPartyTypeID int, InterestedPartyType   nvarchar(30), Muni int ) ' )

Open in new window


>>2. Now that I have a unique name and created a table using that name, in what form would I need to pass the table name to a  SPROC executed form the main routine?  Would it be as simple as passing the name as a nvarchar field (tempname)?
You can pass the name as below
EXEC yourSpName @TName, otherparameterValues

Open in new window

0
Pawan KumarDatabase ExpertCommented:
In this yourSpName you have to use like below to get data from the temp table.
EXEC ( ' SELECT * FROM ' + @TName ) 

Open in new window

1
Nitin SontakkeDeveloperCommented:
@Pawan,

My understanding is that for this temp table to be available to other SPROCS it would have to have a ## prefix rather than #.

The answer to this should be 'Yes'. You have responded with 'No'. Please read the statement carefully. You have stated exactly the same what OP has said in above statement.

Or tell me if I am misreading something...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
@Nitin - Yes it is typo.
0
ValentinoVBI ConsultantCommented:
Actually, as long as the main sproc is calling other sprocs and the temporary table doesn't need to exist anymore after the main sproc finishes then a local temporary table (using a single #) will do just fine.

a local temporary table that is created within a stored procedure or session is dropped when it is finished so it cannot be referenced by the process that called the stored procedure that created the table. It can, however, be referenced by any nested stored procedures executed by the stored procedure that created the table.

Ref. Temporary Tables in SQL Server
0
PortletPaulfreelancerCommented:
tiny alternative: instead of concat()

         select quotename(  REPLACE(NEWID(),'-','')  )
1
Scott PletcherSenior DBACommented:
You can use a normal temp table, since a temp table created in a proc will be available to any proc called by that proc.  SQL will then automatically ensure a unique name for you.

As to passing that table to another proc, yes, you can just pass the name, if you are willing for the called proc to use dynamic SQL to access that temp table.  It would be much easier to simply use a specific temp table name(s) and allow the called procs to use the name directly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks for all of the replies

Scott: Just want to clarify.  It sounds like I don't need the unique name or to pass the temp table name as a parameter.

1. So If I User1 creates a temp table in SProc1 called #TempBlah and Sproc1 executes Sproc2, Sproc2 can use #TempBLah without passing any parameters?  Internally the name #TempBlah is unique to this session?

2.  If user2 initiates Sproc1, while user1 is still executing, and user 2's SPROC1 creates #TempBlah, then executes SPROC2:  user 2's SPROC2 won't use #TempBlah created by user1.  It will know to use #TempBlah created by User2.

Is that correct?  If so then I see that there would be no need to give each #temp table a unique name or pass the #temp table name as a parameter.
0
Scott PletcherSenior DBACommented:
1. Yes
2. Yes

That is the big built-in advantage of temp tables: SQL itself add chars to make every separate users' physical table name different from each other, but the code can use the same logical table name, such as #TempBlah, and each user will only see their own version of that table.
0
mlcktmguyAuthor Commented:
Thanks for all of the information.

Scott provided information that made my original request unnecessary but I acknowledge that Pawan did provide an answer to the questions I asked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.