Dynamic SQL - Drop Table
-
I have an application that fires off several stored procs sequentially. The first process creates a table, "tmpSalesRollUpUserId" (unique to the user). Subsequent procs use this table for gathering and querying information. I need that last proc to drop this table. I am having difficulties doing this, though it seems like an easy task.
The table is dynamically named based on the UserId. I use Dynamic SQL to create the table and insert records and that works just fine D-SQL is necessary for a few additional reasons. I used Dynamic SQL to drop the table and this will not work. If I had parameter @tableName, what would be the correct syntax to exec a drop table command through Dynamic SQL?
Thanks!
(MSSQL2000)
-
The table might be created with userid as owner (user.table instead of dbo.table) so when u drop u will have to specify user.table.
-
declare @tableName sysname -- your parameter
set @tableName = 'dbo.tmpSalesRollUpUserId'
declare @sqlCmd nvarchar(4000)
set @sqlCmd = N'drop table ' + @tableName
exec (@sqlCmd)
hth!
-
You may want to wrap this in a test for existence...
DECLARE @TableName varchar(100),
@SQL varchar(1000)
SET @TableName = 'dbo.YourTableName'
SET @SQL =
'IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N' + CHAR(39) + @TableName + CHAR(39) +') ' + CHAR(10) +
' AND OBJECTPROPERTY(id, N' + CHAR(39) + 'IsUserTable' + CHAR(39) + ') = 1) '
+ CHAR(10) + 'PRINT ' + CHAR(39) + 'WORKS' + CHAR(39) + CHAR(10)
EXEC( @SQL)
I would also question the need to generate physical tables and drop them... Is this maxing out your transaction log?
I wasn't born stupid - I had to study.
-
I feel the need to point out that temporary tables where created exactly for those kind of process! May we know what you are trying to execute will that pseudo temp table and all those procs??
-
Remi is right; further you can create temporary stored procedures as well....so that everything is maintained in an individuals session; the combination of temp tables and temp procs would be a better solution in my opinion.
here's a crappy example:
--for users who are too lazy to type "SELECT * FROM"
CREATE procedure #show
--USAGE: #show sometable
@TblName varchar(128)
--WITH ENCRYPTION
As
Begin
exec('Select * from ' + @TblName)
End
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, somhow crappy doesn't quite say it for me with this code
.I hope he has some better uses for his temp objects!!
-
yeah a powerful procedure like that....falling into the wrong hands...i usually keep that one locked up using the WITH ENCRYPTION statment...it's just too much for some people....
this is just another useless post pushing me closer to the 600 post mark.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is this really how you want to make it?? Why don't you go help someone instead... gonna make you feel a lot better. Believe me, I've been there 13 times already
.And now only 7 more posts to pass Steve (7838)
. -
Holy cow, what an abundance of help. I really appreciate them all!
I generally use #temptables, however, in this app, those tables drop off after the sproc that created them has finished executing. Tough to provide all the reasons without writing a full report, but basically, the temp table rolls up data into a few thousand rows versus 30 million. The data rolled up is based on a web form that a user fills out and submits.
I basically just have the need to maintain that table without having to rebuild it for each sproc that uses it. Maybe I've missed something, but using actual tables is the only way I've been able to get it working, though I have understood that is not ideal.
Backup mode is set to SIMPLE since the database is specific to this application and does not need additional recovery options.
Thanks again!
-
You can make it work like this :
Create a master proc above them all.
Create the temp table in that proc. Then have that proc call all child processes. Then destroy the temp table in the master proc (just to make sure it doesn't hang around for nothing).
That should work but it's hard for me to tell if it can be applied to your specific case. Also what happens if the same user hits the submit button twice in a row or from 2 different windows?
I would change the userid part in the name to spid with is the connection id (select @@spid). That way you'll never run into troubles.
-
Ugh, my session expired after typing my post!
I do have a master proc but not until after recompiling the code. I must not have tried the #temp table since then. That does lead to another question: Will the #temp table be available for nested sprocs? For instance, if the master sproc launches child sproc1 which then launches child sproc2, would I still be able to access the #temp table.
Point taken on the @@spid. My web app takes care of double clicks. The name for temp table is not necessary, and maybe I'll change it, but it does not create any conflicts that I can tell after repeated testing.
Thanks again!
-
Yes, #temp table is available for nested sprocs.
Create PROC dbo.Bunch
AS
CREATE TABLE #temp (...)
exec dbo.Step1
exec dbo.Step2
exec dbo.ReportResults
GO
#temp will be available in all Step and Report procedures. As you know it will be dropped automatically as SP Buch is finished. Don't bother to drop it in code.
And you don't need dynamic SQL anymore.
_____________
Code for TallyGenerator -
Don't bother dropping it????
So what happens if all your application stopped using the lines :
MyCn.Close
SET MyCn = Nothing.
Would you still consider this a good pratice
?? -
Agree. It is always a good idea to drop the temp table rather than relying on SQL to clean up when the table passes out of scope.
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply