Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-30 : 07:15:32
|
| Steve writes "HelloI am building a stored procedure this is getting passes a variable table name I.E. (MyTable_34567) into @variableTable where 34567 is going to change every time the Proc. is called by the application.The Proc must Create a table named MyTable_34567 and give attributes like TableId int identity, name varchar(100) for example.is there any way to do this inside the stored procedure beside Dynamic SQLdeclare @sql varchar(1000)select @sql ='create table'select @sql= @sql+ @variableTable+ ' (tableId int identity, name varchar(100)'exec(@sql)I beleive dynamic SQL will slow Porc. down and ths Proc. is going to be a biggie.Any help you can think of Thanks Steve" |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-30 : 07:37:53
|
| Nope, the only choice is dynamic sql and seems that you know dynamic sql is slow and prone to security problems. What kind of database design requires you to create numbered tables on the fly? If you can share your business requirements, we should be able to suggest a better approach.OS |
 |
|
|
shebert
Yak Posting Veteran
85 Posts |
Posted - 2004-06-30 : 10:30:43
|
| I have a VB application and when you click a button it passes several variables to the Stored Proc. one being a temporary tables that the proc. is to populate wtih expense report data. this data will then be displayed inside the VB app window. another button will be pushed to getnerate an text output file from the temp table the delete the table.I believe that a temporary/variable table name is be handed to proc. so many people at the same time can pull data with slighly different date ranges and create files.Thanks for the help Steve |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-30 : 15:26:37
|
| Couldn't you have a permanent table with a "Batch Number" column instead?Kristen |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-30 : 15:38:32
|
| Whats wrong with the standard temp table?? You could Regenerate the output data with another SP If the second Button is pressed.JimUsers <> Logic |
|
|
|
shebert
Yak Posting Veteran
85 Posts |
Posted - 2004-06-30 : 15:51:01
|
| how would you populate the VB window using a standard temp table to hold the data?would the standard temp table go away after proc. executed?Steve |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-30 : 16:06:21
|
| > would the standard temp table go away after proc. executed?yes, so it won't be there when you want to do "another button will be pushed to getnerate an text output file from the temp table the delete the table."(Hence my suggestion to add a Batch Number column)Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-30 : 17:29:02
|
| Don't you have things like recordsets for this????????????????? I'm confused?!?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-30 : 23:34:36
|
| I was assuming it was a web app. Its actually a VB app, so that seems a much better approach!Kristen |
|
|
|
|
|
|
Passing a variable table name into your stored Proc.