Changing a field without knowing which field to change

Changing a field without knowing which field to change

Post by Aaron Barke » Fri, 21 Feb 2003 01:20:57



I have a stored procedure I want to write that changes a field in the DB
based on a variable column name. Below is the snippet of code I thhought
might work, but alas no dice. Using Query Analyzer I get the message (1) row
affected, but no changes to the data are apparent. Any ideas??

Thanx,

A

CREATE PROCEDURE hvp_NavChangeField





AS







GO

 
 
 

Changing a field without knowing which field to change

Post by Aaron Bertrand [MVP » Fri, 21 Feb 2003 02:04:41


Why not pass all the values into the stored procedure, whether they have
changed or not?  Then -

(a) you don't have to worry about convoluted logic to determine how to
construct your UPDATE statement;
(b) people will be able to update more than one value during each run.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


> I have a stored procedure I want to write that changes a field in the DB
> based on a variable column name. Below is the snippet of code I thhought
> might work, but alas no dice. Using Query Analyzer I get the message (1)
row
> affected, but no changes to the data are apparent. Any ideas??

> Thanx,

> A

> CREATE PROCEDURE hvp_NavChangeField





> AS







> GO


 
 
 

Changing a field without knowing which field to change

Post by Richard Chandle » Fri, 21 Feb 2003 02:46:40



Quote:> I have a stored procedure I want to write that changes a field in the DB
> based on a variable column name. Below is the snippet of code I thhought
> might work, but alas no dice. Using Query Analyzer I get the message (1)
row
> affected, but no changes to the data are apparent. Any ideas??

You get 1 for the row count because the update matches one row, but changes


inside the procedure. It presumably matches one row because XXX_Cust_ID is
unique within your table.

R.

 
 
 

Changing a field without knowing which field to change

Post by Joe Celk » Fri, 21 Feb 2003 02:52:09


Quote:>> I have a stored procedure I want to write that changes a field [sic]

in the DB based on a variable column name. <<

Don't write code this way.  What you are saying is that "I have no idea
what this procedure will do until some user -- present or future --
decides for me!"  

Look at your attempted code -- you have no idea what column (NOT field!)
will be changed, nor any idea what data type the target is, so you have
to use VARCHAR(n) and hope the automatic casting will do this correctly
for you.  You don't even know which table will be affected.

Look up coupling and cohesion in your old software engineering books.
This is not the way to write a module.  

Now, the question I have been asking at the end of most of my posts (and
getting angry reactions to): What is your first or most used programming
language?  Does it have features that support what you were trying to do
in SQL?

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Changing a field without knowing which field to change

Post by Aaron Barke » Fri, 21 Feb 2003 04:31:07


Only one value at a time is ever udpdated and the values of the other

columns are not always available at the time of the update.

Thanx,

A



> Why not pass all the values into the stored procedure, whether they have
> changed or not?  Then -

> (a) you don't have to worry about convoluted logic to determine how to
> construct your UPDATE statement;
> (b) people will be able to update more than one value during each run.

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



> > I have a stored procedure I want to write that changes a field in the DB
> > based on a variable column name. Below is the snippet of code I thhought
> > might work, but alas no dice. Using Query Analyzer I get the message (1)
> row
> > affected, but no changes to the data are apparent. Any ideas??

> > Thanx,

> > A

> > CREATE PROCEDURE hvp_NavChangeField





> > AS






AND

> > GO

 
 
 

Changing a field without knowing which field to change

Post by Aaron Barke » Fri, 21 Feb 2003 04:34:46


The language is Visual Basic 6. All fields to be updated are VarChar. The
intent of the code is to be a generic update wrapper for the columns in
several tables using a standard set of variables. IE. TableName, ColumnName,
Value. I thought this would be the "Best way", but based on the responses, I
guess it isn't.  :)

Thanx,

A


Quote:> >> I have a stored procedure I want to write that changes a field [sic]
> in the DB based on a variable column name. <<

> Don't write code this way.  What you are saying is that "I have no idea
> what this procedure will do until some user -- present or future --
> decides for me!"

> Look at your attempted code -- you have no idea what column (NOT field!)
> will be changed, nor any idea what data type the target is, so you have
> to use VARCHAR(n) and hope the automatic casting will do this correctly
> for you.  You don't even know which table will be affected.

> Look up coupling and cohesion in your old software engineering books.
> This is not the way to write a module.

> Now, the question I have been asking at the end of most of my posts (and
> getting angry reactions to): What is your first or most used programming
> language?  Does it have features that support what you were trying to do
> in SQL?

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. Change one field when another field changes?

Two related questions:

1. What is the best way to *automatically* trigger a change to a field
in a record when *any* other field has changed?  I'd want to have the
change reflect in both the record and in the data-aware control for
the affected field (it could be that changing the data-aware control
would be enough since the content would presumably wind up in the db
field).

An example would be a Last Changed field on an address record.  If an
edit happens, I'd want the Last Changed field to flip to today's date
on the first edit to any part of the record.

2. Now a the narrower case: we want to change one field only when a
specific other field (or data-aware control) changes.

Ideas appreciated, thanks.

--
Frank Burleigh 812-333-7082 (h); 812-855-9170 (o); 812-855-0555 (fax)
School of Law 254, Indiana University, Bloomington, IN  47405

2. Developer 2000 to connect Oracle Personal 8.1.6

3. Help. FPW2.6 Changing browse field without moving

4. Sql query performance help

5. Can I change field length without dropping table????????????

6. Security Exception in Database access Applet

7. Changing Key Fields Without Orphaning Linked Records

8. VB 3 ?????

9. Changing Key Fields in Master Record without Orphaning Detail Records

10. PLS help: Change field structure without loosing data?

11. Totals Page Changes With Field Change

12. String field in DBGrid not changed, but change is loged in Delta

13. Totals Page Changes With Field Change