Alter Table

Question

Alter Table

RAJ-164224

Grasshopper

Points: 10
More actions
February 20, 2007 at 1:10 pm

#116281

Hi,
I want to execute a script that will check if a column exists in the table. If not then add's the column to the existing table and then updates its values. I created the following script but it fails giving the error message "Invalid column name 'AlertsEligible'." Can any one tell me what is the problem with the script?
DECLARE @intRetVal numeric
DECLARE @bDeleteCol varchar(5)
DECLARE @sSQL varchar(4000)
BEGIN
--Set the following line to TRUE if you want to delete the column
SET @bDeleteCol = 'FALSE'
Use PSCommerce
--Check if column exists
if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='AccountSummaryBizRules' and COLUMN_NAME='AlertsEligible' )
Begin
  Select @intRetVal = 1
End
else
begin
  Select @intRetVal = 0
end
Print @intRetVal
--Check if the column needs to be deleted
IF @bDeleteCol = 'FALSE'
BEGIN
  IF @intRetVal = 0
  BEGIN
   --Column does not exist
   BEGIN TRANSACTION
   SET QUOTED_IDENTIFIER ON
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   SET ARITHABORT ON
   SET NUMERIC_ROUNDABORT OFF
   SET CONCAT_NULL_YIELDS_NULL ON
   SET ANSI_NULLS ON
   SET ANSI_PADDING ON
   SET ANSI_WARNINGS ON
   COMMIT
   BEGIN TRANSACTION
   ALTER TABLE AccountSummaryBizRules ADD
    AlertsEligible bit NOT NULL CONSTRAINT DF_AccountSummaryBizRules_AlertsEligible DEFAULT 0
   COMMIT
  END
  if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='AccountSummaryBizRules' and COLUMN_NAME='AlertsEligible' )
  Begin
   BEGIN TRANSACTION
   --Update All except CreditCards to be Alerts Eligibe.
   UPDATE AccountSummaryBizRules SET AlertsEligible = 1 WHERE ApplType <>'50'
   COMMIT
  End
END
ELSE
BEGIN
  --Delete Column if it exists
  IF @intRetVal > 0
  BEGIN
   BEGIN TRANSACTION
   --Drop Constraint
   ALTER TABLE AccountSummaryBizRules
    DROP CONSTRAINT DF_AccountSummaryBizRules_AlertsEligible
   --Drop Column
   ALTER TABLE AccountSummaryBizRules
    DROP COLUMN AlertsEligible
   COMMIT
  END
END
END

You can test this by creating a DB named PSCommerce and add the table using
CREATE TABLE [AccountSummaryBizRules] (
[AcctDesc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcctType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShowBal] [bit] NOT NULL ,
[ShowDetail] [bit] NOT NULL ,
[ShowHist] [bit] NOT NULL ,
[XfrFrom] [bit] NOT NULL ,
[XfrTo] [bit] NOT NULL ,
[BPFunding] [bit] NOT NULL ,
[Active] [bit] NOT NULL ,
[OFXName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstatementEligible] [bit] NOT NULL CONSTRAINT [DF_AccountSummaryBizRules_EstatementEligible] DEFAULT (0)
) ON [PRIMARY]
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

since the column does not exist, sql does not like the drop column statement and that is throwing an error.

you would want to have your sql that actually does either the dropping or adding of the column to be dynamic. meaning, setup the sql string of your statement into your @sSQL variable based upon the @intRetVal variable.

Once you have the sql statement put together in the variable, run: exec (@sSQL)

hope this helps

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Run the script in query analyzer. WHen you get the error message, right click on the error and it will highlight the line in your code where it believes the error exists. Let us know where that is.

-SQLBill

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Ummmm.... I gotta know.... WHY are you trying to do this in a script?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RAJ-164224 Grasshopper Points: 10 More actions · Answer 4

Chuck: Yes, the dynamic SQL did the trick but it was not the Drop Column that was causing the issue, it was the Update statement since the field did not exist.

Jeff: The reason I am doing this is to create a script to update a current prod system. So I need to add the column to the existing table. But before doing that I need to check if one had already added the column manualy. Then if it is already added or I successfully added the column, I need to update the field for certain rows and thats the Update field. The delete is there in case they want to undo the cahnges to the system and being the db back to its original state in case somehting fails.

-Raj

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

So, basically, it's a data model change with a cleanup script to boot and not a permanent stored procedure?

If that's true, then just add a GO on a separate line after the column is created and everything will work just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)