what is the wrong with this - 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

SQL Server Development (2000)

what is the wrong with this

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-11-26 : 04:16:27

declare @a int
update t_cbs_rqst
exec das @a output
set rqst_cd=@a

regards
dasu.g

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-26 : 05:06:09

It would seem to me you want to do:

declare @a int --declare variable
exec das @a output --assign value

update t_cbs_rqst set rqst_cd=@a --update table with value

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

dasu
Posting Yak Master

104 Posts

Posted - 2004-11-26 : 05:38:30

my requirement is i have to call stored procedure in update statement
the stored procedure will take some input parameters which are from
update statement
regards
dasu.g

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-26 : 05:57:21

Sounds to me like you need to make it a function ... trying a test example atm

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-26 : 05:59:45

Obviously, you will have totally different processing and table structure's, but see if this example helps you:


-- create proc das @a int output
-- as
-- begin
-- set @a = @a *2
-- end

-- create function f_das (@a int)
-- returns int
-- as
-- begin
-- set @a=@a*2
-- return @a
-- end

set nocount on
declare @ao int
set @ao = 1
--exec das @ao output
select @ao
select rg_scrap_pad.dbo.f_das (@ao)
select @ao
-- create table test_up_proc
-- (val	int)

-- insert into test_up_proc values (1)

select * from test_up_proc

update test_up_proc
set val = rg_scrap_pad.dbo.f_das (@ao)

select * from test_up_proc

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

dasu
Posting Yak Master

104 Posts

Posted - 2004-11-26 : 06:27:57

actuallly iam doing some data manipulation activities in that storedprocedure this is not possible for functions .
i think u understatnd my problem.
regards
dasu.g

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-26 : 07:17:19

So what is das doing to @a ?
-- exec das @a output

suggestion:
Put the UPDATE inside the procedure.

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-26 : 07:57:41

Dasu,

If you are sure you cannot do the manipulation in the function, and you have to do the manipulation during the "update statemnt", as opposed to my earlier suggestion to doing the manipulation, then updating the table, it seems your only remaining option is, as rockmoose suggested, to do the update as part of the procedure.

Can I ask why it is you cannot do the manipulation immediately prior to the update, and having both of them inside a transaction, to ensure consistency?

e.g.


declare @a int --declare variable
declare @rc int --return code - to be populated by SP to confirm success
begin transaction
exec @rc=das @a output --assign value
if @rc <> 0
  begin
  Print 'Error in Proc DAS - aborting and rolling back'
  Rollback transaction
  goto EndPoint
end
update t_cbs_rqst set rqst_cd=@a --update table with value
if @@error <> 0
  begin
  Print 'Error in updating table t_cbs_rqst - aborting and rolling back'
  Rollback transaction
  goto EndPoint
end
commit transaction

EndPoint:

This ensures that if either the procedure or the update fail, your work was rolled back (unless your DAS procedure does transaction and commits).

Does that resolve you reason for doing them "together"?

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources