How to conditionalize UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to conditionalize UPDATE

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-20 : 12:26:07

I have a database that supports dynamic schema - I can't count on columns existing. From an asp.net app I am executing a dynamic SQL statement that runs UPDATE, but I don't know whether the column I am updating exists. I tried this:

IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AA' and COLUMN_NAME = 'GID_CreatedBy_US')
BEGIN
UPDATE AA
SET
GID_CreatedBy_US = 'f0272467-3ec5-48f7-5553-987900b57a11'
WHERE GID_CreatedBy_US IS NULL
END

but I am getting error 207, "Invalid column name 'GID_CreatedBy_US'". Apparently the IF statement somehow doesn't affect UPDATE. If I run:

IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AA' and COLUMN_NAME = 'GID_CreatedBy_US')
BEGIN
PRINT 'exists'
END
ELSE
BEGIN
PRINT 'doesnt exist'
END

I get 'doesnt exist'.

What am I missing?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-20 : 13:03:12

One option is to use the EXECUTE command to execute your UPDATE statement:

IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'AA' and COLUMN_NAME = 'GID_CreatedBy_US')
BEGIN
EXECUTE ('UPDATE AA
SET GID_CreatedBy_US = ''f0272467-3ec5-48f7-5553-987900b57a11''
WHERE GID_CreatedBy_US IS NULL')
END

SQL Server Helper
http://www.sql-server-helper.com

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-20 : 14:10:39

oye....

Want to tell us how a dynamic Schema actually works?

What for example, do you do with data paramters that are passed for the update, but does not existys?

Do you just disregard the data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 15:06:27

The definition of "Dynamic Schema" is:

"A technique by which you make your database much slower, harder to maintain, and more complicated than it needs to be by ignoring standard relational database concepts such as normalization."

If your schema is not a constant, then you should probably not be using a relational database. Store everything in XML or something. If you cannot count on your schema to be consistent, then every single SQL statement that you ever run against your DB must be dynamically created.

- Jeff
http://weblogs.sqlteam.com/JeffS

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-20 : 20:24:52

quote:
Originally posted by sshelper

One option is to use the EXECUTE command to execute your UPDATE statement:

Thank you so much, this did the trick.

Marioi
Posting Yak Master

132 Posts

Posted - 2007-02-20 : 20:42:25

quote:
Originally posted by X002548

Want to tell us how a dynamic Schema actually works?

The schema changes infrequently so UPDATE and INSERT errors don't come up often. I would have to ask another developer about details on what happens when a rowset commit encounters added or removed columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources