SQL Server Stored Procedure

Trying to create a stored procedure that references a temp table who names ends in the userid (table_userid).  Need to know how to reference the userid within my stored procedure because we have many users and each time the user wants to pull up specific data, a temp table is created just for their updates (table_userid)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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:
Please check all these, you may need one of them

SELECT USER_ID('guest');   /*guest name of the user*/
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to add the user name to the temporary table.
Temporary tables only exists within the user connection context, meaning that they all may have the same name as each one will be only seen by the connection that had created it.
deer777Author Commented:
I want to include this info in my from statement within my stored procedure:

from tablename(_username)

Not sure how to add that username on end.  The first part of the table name is always the same.  Only the userid is different per user.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
Just create a temp table. Everytime user connects they will be in different sessions so everything will work fine.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to use dynamic SQL, meaning that you must put the SELECT into a string variable and then execute it:

SET @MySelect = 'SELECT * FROM tablename_' + SUSER_NAME()


Open in new window


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
Pawan KumarDatabase ExpertCommented:
Just create a temp table. Everytime user connects they will be in different sessions so everything will work fine. once they get disconnected temp table will be gone. One temp table will not interfere with another if they are in different sessions unless global temp table.
Pawan KumarDatabase ExpertCommented:
To create dynamic temp table with use name you need to do like below - Also note that you need to use Square brackets as user name may have some space or \  in the user name other wise it will break

Note - below code is tested..

DECLARE @TempTableName AS VARCHAR(200) = '[#YourTempTableName_' 
--PRINT ('SELECT * INTO ' + @TempTableName + @User + ']' + ' From yourTablName' ) 
EXEC ( ' SELECT * INTO ' + @TempTableName + @User + ']' + ' From yourTablName' ) 
--PRINT ('SELECT * FROM ' + @TempTableName + @User + ']' )
EXEC ('SELECT * FROM ' + @TempTableName + @User + ']' )

Open in new window

I don't think you understand the problem or you missed some information from your question and it is us that don't understand the requirements. If you create that "temp" table inside the stored procedure then you need to create a real temporary table, which you do simply by prefixing its name with the pound character: #

CREATE TABLE #tablename(...)

That is the only difference from a permanent table and the fact that they are created automatically inside the tempdb system database. If you do that no matter how many users will use the stored procedure at the same time they will each have their own version of that table that will be completely distinct from others each with whatever the users stores in it. You can drop it at the end of the stored procedure or you can simply ignore it because it will be automatically dropped by the SQL server engine as soon as the connection that created it is closed.

This is the right way to do it unless you want to use the table OUTSIDE the stored procedure that created it, in which case you would have to create a permanent table(with no #).
Scott PletcherSenior DBACommented:

That's not necessarily true.  It sounds to me like they need this table to persist.  The user will call up the data, it will go into the table, and then the user does something else to that data.  During that time, you don't want to maintain the connection to SQL nor do you want to lose the user's data selection(s).  A customized but permanent table seems reasonable to me, rather than a vanishing temp table that also can't be seen or used by anyone else, even someone administering the system.

Correct, that is one of the possibilities, which I mentioned at the end of my post. In that case only dynamic query can solve the problem so the asker chose that. However, for this scenario I would have probably used one table for all with a column that would identify the user plus an identity column and a CreatedDate column, rather than create a table each time the procedure is executed, which needs to be dropped before is created again. I am not sure what he is doing, though.
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

From novice to tech pro — start learning today.