how to call a procedure in update statement

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 call a procedure in update statement

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-11-26 : 06:31:56

i have following situation

i need to call a stored procedure for each and every record of t_cbs_rqst table while updating t_cbs_rqst table.
iam sending u the model
so please suggest me the solution

stored procedure:

CREATE PROC CALLING @A INT OUTPUT
AS
SET @A=1

update statement :
here in calling stored procedure we need to pass the record as input parameter and rqst_cd is out put parameter

declare
@a int
UPDATE T_CBS_RQST
SET rqst_cd=exec CALLING @a output

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-26 : 06:34:02

You will have to loop through each record calling the the sp for each one.
Another option is to create a function or create a temp table which the SP acts on to calculate the new values.

You should probably review the design of what you are trying to do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dasu
Posting Yak Master

104 Posts

Posted - 2004-11-26 : 06:44:40

UPDATE d
SET
d.stat_cd=dbo.fn_cbs_get_stat_cd(d.acct_no,d.val_dt,d.rqst_cd,d.stat_cd,d.upd_job_id ),
d.upd_job_id=a.job_id
FROM T_CBS_RQST_DTLS_ADT a,T_CBS_RQST_DTLS d
WHERE
d.acct_no = a.acct_no
AND d.val_dt = a.val_dt
AND d.rqst_cd = a.rqst_cd
AND d.sys_cd =a.sys_cd
AND d.stat_cd<>a.stat_cd
AND d.upd_job_id <> d.job_id
AND d.upd_job_id=@pintJob

in above querry i am calling function
that is
dbo.fn_cbs_get_stat_cd(d.acct_no,d.val_dt,d.rqst_cd,d.stat_cd,d.upd_job_id )

but functions are not supporting data manipulations so above function is giving errors so i made it as stored procedure so
but the problem is iam unable to use that.
can u tell me ways to use stored procedurds in update statemetn
thanks
dasu.g

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-27 : 01:45:19

quote:
Originally posted by dasu
but functions are not supporting data manipulations so above function is giving errors

what data manipulations ?. What are you trying to do in the fuction? Update other tables? etc...

Can you not use triggers to update this column.

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources