February 20, 2007 at 1:10 pm
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
February 20, 2007 at 2:47 pm
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
February 20, 2007 at 3:51 pm
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
February 20, 2007 at 7:20 pm
Ummmm.... I gotta know.... WHY are you trying to do this in a script?
--Jeff Moden
February 21, 2007 at 11:20 am
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
February 21, 2007 at 5:21 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply