Calling a stored procedure through an UPDATE statement?

Hi,
I'm trying to create several insert statements in a stored procedures
as in below, but it will insert the reocrds sequentially and i want the
stored procedures to populate the fields that are parrallel. I also tried
set rowcount 0 - it didn't accept that and I don't know how to use the
UPDATE for
this like "set Count_H = exec LOE_H_Counts" and it didn't work.
Does anybody know how I can call a stored procedure in an UPDATE clause or
if there is another way to get around this?
Thank you so much for your time!
CREATE Procedure LOE_Counts
as
create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
Count_H int, Count_M int, Count_L int, Count_Total int)
insert #tempLOE(id, Status, TYPE, Count_Total)
exec LOE_GrandTotal
insert #tempLOE(Count_H)
exec LOE_H_Count
insert #tempLOE(Count_M)
exec LOE_M_Count
select * from #tempLOEYou might want to look into user defined functions. You can use them to
return tables or scalar values.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
"SK" <SK@.discussions.microsoft.com> wrote in message
news:AB73E48F-B1D0-43FA-8C34-F1F60A607C10@.microsoft.com...
> Hi,
> I'm trying to create several insert statements in a stored procedures
> as in below, but it will insert the reocrds sequentially and i want the
> stored procedures to populate the fields that are parrallel. I also tried
> set rowcount 0 - it didn't accept that and I don't know how to use the
> UPDATE for
> this like "set Count_H = exec LOE_H_Counts" and it didn't work.
> Does anybody know how I can call a stored procedure in an UPDATE clause or
> if there is another way to get around this?
> Thank you so much for your time!
> CREATE Procedure LOE_Counts
> as
> create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
> Count_H int, Count_M int, Count_L int, Count_Total int)
> insert #tempLOE(id, Status, TYPE, Count_Total)
> exec LOE_GrandTotal
> insert #tempLOE(Count_H)
> exec LOE_H_Count
> insert #tempLOE(Count_M)
> exec LOE_M_Count
> select * from #tempLOE|||Unfortunately you cannot use the the result from the stored procedure
automatically for an update
The round about way is to set an output parameter in the called SP
and then store it in a variable and use it for update.
if the SP returns a result set, then store it in a temp table as you had
mentioned and join it in the update clause.
Hope this helps|||>>I'm trying to create several insert statements in a stored procedures
as in below, but it will insert the records [sic] sequentially and i
want the
stored procedures to populate the fields [sic] that are parrallel
[unh?]. <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.
In SQL, the INSERT, UPDATE and DELETE statements work on rows as a
unit. This is not a file system, wher you can move a read/write head
to a field and do your work. This is what happens when you use the
wrong words.
I will not even get into the vagueness of "id", "type" and "status" as
attempts at data element names.
Next, good SQL programmers avoid UDF and procedural code. That is 3GL
programming and not declarative coding.|||Thank you Omnibuzz & Chuck,
This is finally what worked. It may not be the best way, but it'll do for n
ow
until I try your suggestions about output parameters and UDF, which I have
not done before.
CREATE Procedure LOE_Counts
as
create table #tempLOE( Status varchar(50) ,Type varchar (50) ,
Count_Total int)
insert #tempLOE( Status, TYPE, Count_Total)
exec LOE_GrandTotal
create table #Hcount(Hstatus varchar(50), Htype varchar (50),Count_H int)
insert #HCount(Hstatus, Htype, Count_H)
exec LOE_H_Count
create table #Mcount(Mstatus varchar(50), Mtype varchar (50),Count_M int)
insert #MCount(Mstatus, Mtype, Count_M)
exec LOE_M_Count
create table #Lcount(Lstatus varchar(50), Ltype varchar (50),Count_L int)
insert #LCount(Lstatus, Ltype, Count_L)
exec LOE_L_Count
select * from #tempLOE inner join #HCount on #tempLOE.Status+ #tempLOE.type=
#HCount.Hstatus+ #HCount.Htype
inner join #MCount on #tempLOE.Status+ #tempLOE.type= #MCount.Mstatus+
#MCount.Mtype inner join
#LCount on #tempLOE.Status+ #tempLOE.type= #LCount.Lstatus+ #LCount.Ltype
Have a great day!
SK|||I think you're trying to be helpful. But you only negate everything rather
than understanding what was needed here and do not provide any solution or
real guidance.
The names are just labels as an example with which to work.
"--CELKO--" wrote:

> as in below, but it will insert the records [sic] sequentially and i
> want the
> stored procedures to populate the fields [sic] that are parrallel
> [unh?]. <<
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to have a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor or in an OVER() clause.
> In SQL, the INSERT, UPDATE and DELETE statements work on rows as a
> unit. This is not a file system, wher you can move a read/write head
> to a field and do your work. This is what happens when you use the
> wrong words.
> I will not even get into the vagueness of "id", "type" and "status" as
> attempts at data element names.
> Next, good SQL programmers avoid UDF and procedural code. That is 3GL
> programming and not declarative coding.
>