Dynamic SQL into stored proc ?
-
Hi,I need to write stored proc that will make a calculation taxes inside a table with several records. table will be created from the stored proc.
Several users will execute from an application this stored proc and I create a table for each user. ( x.table1, y.table1, z.table1. ) with different results.
How could I do this without use dynamic SQL into the stored proc ? (temporary table ? )
ideas will be welcome
-
If I understand you correctly, you are looking to create the same table for many users but name it differently for each user.
If so, prefix the table with the users login name on the server(SYSTEM_USER). That way you will have unique table names created and be able to trace them back to the user.
------------------------------
The Users are always right - when I'm not wrong! -
As long as the users are not a part of the dbo group like Jonathan stated CREATE TABLE and explicitly provide the users name as owner. Then in your sp DON'T reference dbo.object and you should be OK.
Another option would be to use #tables because the SQL server knows which instance goes with which process and your code could also just reference the #table name.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Why don't you create a new column that holds the user values (maybe with a DEFAULT of SUSER_SNAME() and put all rowws into one single table? That way some of the ugly code you'Re about to face might be avoided.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply