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,
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.