Can a table be updated this way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can a table be updated this way?

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-05-17 : 10:56:56

Can a table be updated using a stored procedure with an OUTPUT parameter?

Here is what I am trying to do. I have a stored procedure named qryScoreGeneratorProc that accepts a UserID parameter (-1) a test ID (@intTestID) and has an output parameter @txtResult.

Is it possible to update a table using that output parameter without the use of loops or cursors?

Here is what I tried. It doesn't like me.

UPDATE @tmpReferrals
SET txtScore = qryScoreGeneratorProc -1, @intTestID, @txtResult = txtScore OUTPUT

Is it possible to do this?

Thanks!

Aj

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-17 : 12:21:41

quote:
Can a table be updated using a stored procedure with an OUTPUT parameter?

No. But you could write a UDF that returns a table, join the table to your table and UPDATE. It may be easier to write a scalar UDF.

UPDATE MyTable
  SET txtScore = dbo.UDF_qryScoreGenerator(-1, @intTestID, @txtResult)

But no OUTPUT parameters are allowed. The OUTPUT will be the value(s) returned by the function.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-05-17 : 14:57:44

Thanks Sam! I was afraid of that. What I ended up doing is putting a loop around my update that sets a variable, which in turn sets the database column. It works and it isn't any slower than before, so I guess I shouldn't complain.

Thanks!

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-17 : 15:00:41

How ugly is the sproc?

Can you post it? Or is it massive?

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-05-18 : 09:16:21

212 lines of code. Not massive by any means, but still pretty ugly to sift through.

So, if I changed the sp so it had a RETURN value instead of an OUTPUT parameter, then I could do what I wanted to do in my UPDATE statement right?

Aj

SqlShaun
Starting Member

14 Posts

Posted - 2005-05-18 : 12:16:59

--the sproc

create proc MySproc
(
@ireturn int output
)

as

set @ireturn = 5

--the statement

create table #myt
(
it int
)

--value to update
insert into #myt(it)
values(39)

--variable to accept value
declare @i int

--value assigned to @i
exec MySproc @i output

--table updated via @i
update #myt
set it = @i

select *
from #myt

drop table #myt

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-05-18 : 13:31:31

That is in essence what I did. Except I had to update 2300 rows. So I put it into a while loop and set a variable which in turn updated the table.

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources