Stored Procedure with string name for table name
-
I have a stored procedure that receives the parameter @strManager.
I want to make a table using the following:
SELECT Employees.*
INTO @strManager+'_042005'
FROM Employees
WHERE Employees.Mgr Like '%' + @strManager + '%'
but I get an error at the into statement as I am apparently not concatenating the table name correctly. Any ideas of where I am going wrong?
Thx,
CLM
-
Read this for a solution.
-
Basically the article says not to do what I want to do. But I need a way to create tables with unique names based on the user requesting the data. Each user can request data from 10 base tables and I need it to be editable which I believe a view will not accomplish. Any other ideas?
CLM
-
I'm not sure I'm following here :
10 tables = 10 views, any problems with that?
How are the users gonna request the data?
-
users are viewing from Web (PHP) and retrieving the data from SQL Server. I need to let them retrieve their data and edit/update it for accuracy and then save it back to sql server.
The user needs to edit/update the data that is in the main table and since it may take more than one day to make those corrections, I thought it would be best to make a temp table using the user's id and the name of the table (ie clm_software, clm_books, clm_employees). From what I understand views will not support the edit/update of the data. Once the corrections are made the data will be added to the main table as a new set of data with an updated timestamp which will help to differentiate it from the original data that was used as a starting point.
Does that help clarify what I am attempting to do and why?
CLM
-
You don't need temp tables to do something like this. Views will do the job just fine for this task.
-
ok, let's run a scenario here for example.
say I have a table of software a dept uses. I have 10 items and these were all used in 2004. Now in 2005 they want to update that information WITHOUT loosing what was logged for 2004. How does a view allow this?
CLM
-
I think you could have an instead of update on views that could be coded to reinsert the data in the base tables.
But you could also have a table or even 2 more columns that keep the dates between which the software has been or is being used. But it obviously solves only that problem.
I think you need to be more clear about what you need to be able to accomplish if you want a real usefull answer. From what I see you either need to redesign the db, give the users access to the base table so they can do what they need to or use dynamic sql. Each of these solutions are based on my assumption of what you might need to do. But they also all have they pitfalls.
-
I your users can access only certain rows then you can create a new table relating the base table to the users table, or just add a column to the base table for the userID. We do something similar, in all our tables we have the following columns and only data inserts (no modifications or deletions)
Created By, CreatedDate, UpdatedBy, UpdatedDate.
If you query by UpdatedDate, DESC you will always get the latest record and you will have a history of changes. If you add the userID column, you can create SELECT stored procedures based on the UserID.
Hope this helps.
Karen
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply