Howto Update Table Field? - 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 2005 Forums

Transact-SQL (2005)

Howto Update Table Field?

Author

Topic

hadist
Starting Member

2 Posts

Posted - 2009-04-30 : 03:49:28

Hi, all
In this case i have one database which consist several table, each table have a field elapsed_time (type Int), i just want to update this field on all table with calculates different between now date and submit date using function datediff(day,Submit_Date,getdate())
i try using procedure is work fine.

i have try using function but never fire, for next i will used this function to make query in another application run as startup. (update elapsed_time automatically when user used this application)

my another application only accept SELECT SQL Command only, can't execute store procedure

can anyone help me to solve this problem...

newbie

Hadi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 04:29:25

The short question is:
How should it be possible to do an update if an application can only do SELECT-Statements?

No, you're never too old to Yak'n'Roll if you're too young to die.

hadist
Starting Member

2 Posts

Posted - 2009-04-30 : 05:11:01

i have reference about that, but using database ORACLE
they have 2 function for example update_elapsedtime and update_field
on the update_elapsedtime will be called from function update_field
and in another application call like this SELECT * table(update_field)

CREATE OR REPLACE FUNCTION "HADI"."UPDATE_ELAPSEDTIME"
RETURN NUMBER IS
temp NUMBER;

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE Statement
COMMIT;
temp := 1;
RETURN temp;
END;

CREATE OR REPLACE FUNCTION "HADI"."UPDATE_FIELD"
RETURN Update_TABLE
PIPELINED AS v_temp UPDATE_FIELD_OBJ;

update_ElapTime NUMBER;

CURSOR update_cur IS
SELECT DISTINCT T1.dbid, T1.projectid, T1.projectname FROM main_project T1 WHERE T1.dbid = 0;
update_rec update_cur%ROWTYPE;

BEGIN

update_ElapTime := UPDATE_ELAPSEDTIME;
OPEN update_cur;

LOOP
FETCH update_cur INTO update_rec;
EXIT WHEN update_cur%NOTFOUND;

v_temp := UPDATE_FIELD_OBJ(update_rec.projectid);
PIPE ROW(v_temp);

END LOOP;

CLOSE update_cur;

RETURN;
END;

on my another application i just create query SELECT * Table(update_field) and run as startup

it work fine

How SQL Server 2005 work like that?

it just example way, maybe any other way to implement this case

Thax

Hadi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources