• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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)
0
deer777
Asked:
deer777
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Please check all these, you may need one of them

SELECT SYSTEM_USER
SELECT USER_ID('guest');   /*guest name of the user*/
SELECT CURRENT_USER
0
 
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.
0
 
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.
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:
Just create a temp table. Everytime user connects they will be in different sessions so everything will work fine.
0
 
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:
DECLARE @MySelect VARCHAR(MAX)

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

EXEC(@MySelect)

Open in new window

0
 
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.
0
 
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 @User AS VARCHAR(100) = SYSTEM_USER
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

0
 
ZberteocCommented:
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 #).
0
 
Scott PletcherSenior DBACommented:
@Zberteoc:

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.
0
 
ZberteocCommented:
@Scott

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

Join & Write a Comment

Featured Post

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.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now