Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dropping table using Cursor

Status
Not open for further replies.
aspvbnetnerd

aspvbnetnerd

Programmer
May 16, 2006
278
SE
I want to get all table loop them throug and the drop them

Code: 
[COLOR=blue]DECLARE[/color] @TABLES [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](100)

[COLOR=blue]DECLARE[/color] TEMPTABLE_CURSOR [COLOR=blue]CURSOR[/color] [COLOR=blue]FOR[/color] 
[COLOR=blue]SELECT[/color] [[COLOR=blue]NAME[/color]] [COLOR=blue]FROM[/color] SYSOBJECTS [COLOR=blue]WHERE[/color] [COLOR=blue]NAME[/color] LIKE [COLOR=red]'TEMP%'[/color] AND OBJECTPROPERTY(ID, N[COLOR=red]'IsTable'[/color]) = 1

[COLOR=#FF00FF]OPEN[/color] TEMPTABLE_CURSOR

[COLOR=blue]FETCH[/color] [COLOR=blue]NEXT[/color] [COLOR=blue]FROM[/color] TEMPTABLE_CURSOR [COLOR=blue]INTO[/color] @TABLES

[COLOR=blue]WHILE[/color] @@FETCH_STATUS = 0 [COLOR=blue]BEGIN[/color]
	[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] @TABLES  [COLOR=red]<-- Error[/color]
	[COLOR=green]--PRINT @TABLES		
[/color]	[COLOR=blue]FETCH[/color] [COLOR=blue]NEXT[/color] [COLOR=blue]FROM[/color] TEMPTABLE_CURSOR [COLOR=blue]INTO[/color] @TABLES
[COLOR=blue]END[/color]

[COLOR=blue]CLOSE[/color] TEMPTABLE_CURSOR
[COLOR=blue]DEALLOCATE[/color] TEMPTABLE_CURSOR

Error that I am receiving is this Incorrect syntax near '@TABLES'.

Why does this not work. There is nothing wrong with the sql statement. If I comment out the drop and print out the tables name then it work.

/George
 
Sort by date Sort by votes
Code: 
    [COLOR=blue]exec[/color] ([COLOR=red]'DROP TABLE ['[/color] + @TABLES + [COLOR=red]']'[/color])  [COLOR=green]-- No More Errors[/color]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thank you George.
Now it works, but why didn't it work for me.

I also tried with execute 'DROP TABLE ' @TABLES
 
Upvote 0 Downvote
Why are you creating real tables name temp%? If they are temp tables you would be better off creating real temp tables or table variables.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
exec and execute are the same command. The difference is that I used parenthesis in my version where you didn't use them. The parenthesis are important. [wink]


Also, I put square brackets in the code. So, I am effectively running....

Drop Table [Temp with spaces in name]

Where yours would run...

Drop Table Temp with spaces in name

If you have tables with spaces in the name, my command would still run, but yours would fail. Now, as good developers, we never create a table with spaces in the name, right? But we cannot always guarantee that we are the only ones creating tables, so it is safer to use the square brackets.

By the way... I agree with SQLSister, you shouldn't be creating 'real' temp tables.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
At first I created virtual table with Brackets like #Temp_Buntch.

But from My vb.net connect to the database and create virtual tables next time I connect that table will not still be there. I'm I right?

Thats why I create real table calling them Temp.

/George
 
Upvote 0 Downvote
You're right. The next time you connect to the database, the #Temp_Buntch table will be gone.

It is usually best to create a stored procedure to do all the work, that way, you can easily wrap it in a transaction (preventing data corruption). The general idea is...

1. Create a temp table
2. Fill it with data
3. Use the data from the temp table
4. Drop the temp table

You can do all 4 steps from within a single stored procedure. Aside from the transaction handling, this method allows you to 'hit' the database just once, so your whole process will execute faster.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I have created this procedure. The scannes a document from an application. If a document is missing I have to take care if it in the application.

Code: 
[COLOR=blue]ALTER[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[usp_ValidateDocument]
@RUBRIC [COLOR=blue]VARCHAR[/color](50),
@DOCUMENTTYPEID [COLOR=blue]INT[/color],
@FIRSTSERIALNUMBER [COLOR=blue]INT[/color],
@LASTSERIALNUMBER [COLOR=blue]INT[/color],
@YEAR [COLOR=blue]SMALLINT[/color],
@DAYNUMBER [COLOR=blue]SMALLINT[/color],
@SCANNINGSTATIONID [COLOR=blue]SMALLINT[/color]
[COLOR=blue]AS[/color]

[COLOR=green]--======================================================================
[/color][COLOR=green]--
[/color][COLOR=green]--	Procedure that validate a batch that it comes in order
[/color][COLOR=green]--
[/color][COLOR=green]--	History:
[/color][COLOR=green]--	Date		Signature		Comment
[/color][COLOR=green]--	2007-09-20	GeoIss      	First version
[/color][COLOR=green]--	2007-09-20	GeoIss			1.01
[/color][COLOR=green]--
[/color][COLOR=green]--	1.00		2007-09-20		Procedure created
[/color][COLOR=green]--	1.01		2007-09-20		Validates the scanned buntch. 
======================================================================
[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]DECLARE[/color] @RetLastSerialNumber [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @RetMessage [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](100)
[COLOR=blue]DECLARE[/color] @RetLastSerialNumberTemp [COLOR=blue]AS[/color] [COLOR=blue]INT[/color]


[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'Nothing'[/color]


[COLOR=green]-- Create a temp table if does not exists
[/color][COLOR=blue]IF[/color] NOT EXISTS ([COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] SYSOBJECTS [COLOR=blue]WHERE[/color] ID = [COLOR=#FF00FF]OBJECT_ID[/color]([COLOR=red]'Temp_Buntch'[/color])) [COLOR=blue]BEGIN[/color]
	[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] [dbo].[Temp_Buntch](
	[AssignmentID] [[COLOR=blue]int[/color]] NULL, 		
	[BuntchID] [[COLOR=blue]int[/color]] [COLOR=blue]IDENTITY[/color](1,1) NOT NULL, 
	[FirstSerialNumber] [[COLOR=blue]int[/color]] NULL, 
	[LastSerialNumber] [[COLOR=blue]int[/color]] NULL, 
	[[COLOR=#FF00FF]Year[/color]] [nvarchar](2) NULL, 
	[DayNumber] [[COLOR=blue]smallint[/color]] NULL, 
	[ScanningStationID] [[COLOR=blue]smallint[/color]] NULL, 
	[Lost] [[COLOR=blue]bit[/color]] NULL)
[COLOR=blue]END[/color]


[COLOR=green]-- Check if the assignment exist
[/color][COLOR=blue]IF[/color] NOT EXISTS([COLOR=blue]SELECT[/color] A.ASSIGNMENTID [COLOR=blue]FROM[/color] ASSIGNMENT A 
			  [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
			  [COLOR=blue]WHERE[/color] A.RUBRIC = @RUBRIC AND A.DOCUMENTTYPEID = @DOCUMENTTYPEID 
		  AND A.ACTIVE = 1) [COLOR=blue]BEGIN[/color]
	
	[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'AssignmentDoesNotExists'[/color]

	[COLOR=blue]GOTO[/color] THE_END

[COLOR=blue]END[/color]

[COLOR=green]-- Check if document has already been scanned
[/color][COLOR=blue]ELSE[/color] [COLOR=blue]IF[/color] EXISTS
	([COLOR=blue]SELECT[/color]	B.*
	[COLOR=blue]FROM[/color]	ASSIGNMENT A 
				[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
				[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
				[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
				[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID
	[COLOR=blue]WHERE[/color]	A.RUBRIC = @RUBRIC
	AND		A.ACTIVE = 1 
	AND		B.YEAR = @YEAR
	AND		B.FIRSTSERIALNUMBER = @FIRSTSERIALNUMBER
	AND		B.DAYNUMBER = @DAYNUMBER
	AND		B.SCANNINGSTATIONID = @SCANNINGSTATIONID
	AND		DT.DOCUMENTTYPEID = @DOCUMENTTYPEID) [COLOR=blue]BEGIN[/color]

	[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'DocumentAlreadyScanned'[/color]

	[COLOR=blue]GOTO[/color] THE_END

[COLOR=blue]END[/color]

[COLOR=green]-- Check if document comes in order
[/color][COLOR=blue]ELSE[/color] [COLOR=blue]IF[/color] @RetMessage = [COLOR=red]'Nothing'[/color] [COLOR=blue]BEGIN[/color]

	([COLOR=blue]SELECT[/color] @RetLastSerialNumber = [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](B.LASTSERIALNUMBER), 0), @RetLastSerialNumberTemp = [COLOR=#FF00FF]ISNULL[/color]([COLOR=#FF00FF]MAX[/color](TB.LASTSERIALNUMBER), 0)
	[COLOR=blue]FROM[/color]	ASSIGNMENT A 
				[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
				[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
				[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
				[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID	
				[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID			
	[COLOR=blue]WHERE[/color]	A.RUBRIC = @RUBRIC
	AND		A.ACTIVE = 1
	AND		DT.DOCUMENTTYPEID = @DOCUMENTTYPEID
	AND		B.SCANNINGSTATIONID = @SCANNINGSTATIONID
	AND		B.CARDBOARDID = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](CB.CARDBOARDID)
							 [COLOR=blue]FROM[/color]	ASSIGNMENT A 
								[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CUSTOMER C [COLOR=blue]ON[/color] C.CUSTOMERID = A.CUSTOMERID 
								[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] DOCUMENTTYPE DT [COLOR=blue]ON[/color] DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID 
								[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] CARDBOARD CB [COLOR=blue]ON[/color] CB.ASSIGNMENTID = A.ASSIGNMENTID 
								[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BUNTCH B [COLOR=blue]ON[/color] CB.CARDBOARDID = B.CARDBOARDID
								[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Temp_Buntch TB [COLOR=blue]ON[/color] TB.ASSIGNMENTID = A.ASSIGNMENTID
							 [COLOR=blue]WHERE[/color]		B.SCANNINGSTATIONID = @SCANNINGSTATIONID
							 AND		A.RUBRIC = @RUBRIC
							 AND		A.DOCUMENTTYPEID = @DOCUMENTTYPEID)) 


			[COLOR=blue]IF[/color] @RetLastSerialNumberTemp + 1 = @FirstSerialNumber [COLOR=blue]BEGIN[/color]
				[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'DocumentIsInOrder'[/color]
				[COLOR=blue]SET[/color] @RetLastSerialNumber = @RetLastSerialNumberTemp
			[COLOR=blue]END[/color]
			[COLOR=blue]IF[/color] @RetLastSerialNumberTemp > @RetLastSerialNumber [COLOR=blue]BEGIN[/color]
				[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'DocumentIsMissing'[/color]
				[COLOR=blue]SET[/color] @RetLastSerialNumber = @RetLastSerialNumberTemp
				[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] Temp_Buntch(AssignmentID, FIRSTSERIALNUMBER, LASTSERIALNUMBER, [[COLOR=#FF00FF]YEAR[/color]], DAYNUMBER, SCANNINGSTATIONID, LOST)
							[COLOR=blue]VALUES[/color](2, @FirstSerialNumber, @LastSerialNumber, @YEAR, @DAYNUMBER, @SCANNINGSTATIONID, 1)
				[COLOR=blue]GOTO[/color] THE_END
			[COLOR=blue]END[/color]
			[COLOR=blue]ELSE[/color] [COLOR=blue]IF[/color] @RetLastSerialNumber + 1 = @FIRSTSERIALNUMBER [COLOR=blue]BEGIN[/color]
				[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'DocumentIsInOrder'[/color]
				[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] Temp_Buntch(AssignmentID, FIRSTSERIALNUMBER, LASTSERIALNUMBER, [[COLOR=#FF00FF]YEAR[/color]], DAYNUMBER, SCANNINGSTATIONID, LOST)
							[COLOR=blue]VALUES[/color](2, @FirstSerialNumber, @LastSerialNumber, @YEAR, @DAYNUMBER, @SCANNINGSTATIONID, 1)
				[COLOR=blue]GOTO[/color] THE_END
			[COLOR=blue]END[/color]
			[COLOR=blue]ELSE[/color] [COLOR=blue]BEGIN[/color]
				[COLOR=green]-- Insert the lost LastSerialNumber in 
[/color]				[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] Temp_Buntch(AssignmentID, FIRSTSERIALNUMBER, LASTSERIALNUMBER, [[COLOR=#FF00FF]YEAR[/color]], DAYNUMBER, SCANNINGSTATIONID, LOST)
							[COLOR=blue]VALUES[/color](2, @FirstSerialNumber, @LastSerialNumber, @YEAR, @DAYNUMBER, @SCANNINGSTATIONID, 1)

				[COLOR=blue]SET[/color] @RetMessage = [COLOR=red]'DocumentIsMissing'[/color]
			
				[COLOR=blue]GOTO[/color] THE_END
			[COLOR=blue]END[/color]

[COLOR=blue]END[/color]


THE_END:

[COLOR=blue]SELECT[/color] @RetMessage [COLOR=blue]AS[/color] RetMessage, [COLOR=#FF00FF]ISNULL[/color](@RetLastSerialNumber, 0) RetLastSerialNumber, [COLOR=#FF00FF]ISNULL[/color](@RetLastSerialNumberTemp, 0) [COLOR=blue]AS[/color] RetLastSerialNumberTemp

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]OFF[/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
782
BobSuruncle
BobSuruncle
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
510
Mike Lewis
Mike Lewis
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
588
Chris Miller
Chris Miller
Auguy
  • Locked
  • Question
Using UDF In Select, Bad Idea? 2
Replies
3
Views
299
Auguy
Auguy
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
447
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top