Attempting to drop column a recreate it again

D

davearia

Well-known member
Joined
Jan 4, 2005
Messages
184
Hi All,

This a problem I am having in SQL Server 2000.

In a stored procedure I wish to delete a column and recreate it again after the rest of the stored procedure has executed, Ill explain why.

I have a column numberColumn which is an int and has its identity set on. When a user deletes a row out of the table the numberColumn is no longer sequential. Talking to a D.B.A. at work he recommended I delete the column and recreate by using two lines of SQL as below:
Code: 
ALTER TABLE [dbo].[PigJawSuzzle] DROP COLUMN PigJawSuzzleNumber
ALTER TABLE [dbo].[PigJawSuzzle] ADD PigJawSuzzleNumber INT IDENTITY(1,1)
Trying this in Query Analyzer works a treat, however as part of of my stored procedure it doesnt have the effect I need.

Here is my stored procedure:
Code: 
/*Procedure to allow user to delete records from PIGJAWSUZZLE*/
ALTER PROCEDURE dbo.DELETE_FROM_PIGJAWSUZZLE
	@PigJawSuzzleNumber INT 	
AS

DECLARE @intErrorCode INT
BEGIN TRANSACTION

	DELETE FROM 
		[dbo].[PigJawSuzzle]
	WHERE 
		[PigJawSuzzle].PigJawSuzzleNumber = @PigJawSuzzleNumber
		
	SELECT 
		@intErrorCode = @@ERROR
		
		ALTER TABLE [dbo].[PigJawSuzzle] DROP COLUMN PigJawSuzzleNumber
		ALTER TABLE [dbo].[PigJawSuzzle] ADD PigJawSuzzleNumber INT IDENTITY(1,1)
		
	IF (@intErrorCode <> 0) GOTO PROBLEM
	
    COMMIT TRANSACTION
    
    PROBLEM:
		IF (@intErrorCode <> 0) BEGIN
			PRINT Unexpected error occurred!
			ROLLBACK TRANSACTION
		END
The stored procedure goes as far as deleting the column, but fails in the the creation of the column and also fails to delete the record (the SQL above the two lines in question), which did work until adding these two lines.

The error produced is:
System.Data.SqlClient.SqlException: Invalid column name PigJawSuzzleNumber.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Click to expand...

Proving that the column was removed (obviously I have double checked to be sure) and yet not recreated this column, as I said earlier running these 2 lines of SQL in Query Analyzer works beautifully.

Any ideas please?

Thanks, Dave. :D :D
 
Sounds like you need to add a commit statement in between the column drop and column add. The commit statement makes the change permanent to the database. At anypoint before the commit statement is made, you can rollback your changes to the last commit statement. It works kind of like an undo.

The commit statement will be something like this:

ALTER TABLE [dbo].[PigJawSuzzle] DROP COLUMN PigJawSuzzleNumber
COMMIT[dbo.PigJawSuzzle];
ALTER TABLE [dbo].[PigJawSuzzle] ADD PigJawSuzzleNumber INT IDENTITY(1,1)
COMMIT[dbo.PigJawSuzzle];

Hope this helps.

Chester
 
Back
Top