EXEC of a sproc within another sproc

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy

Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@.EntryLogID int,
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2)
)
AS
DECLARE@.OldCategoryID int

EXEC @.OldCategoryID = BP_GetLedgerCategory @.EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @.ProjectID,
CategoryID = @.NewCategoryID,
Expended = @.Expended

WHERE
EntryLogID = @.EntryLogID

EXEC BP_UpdateCategories @.ProjectID, @.NewCategoryID, @.Expended, @.OldCategoryID

Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@.EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @.EntryLogID

RETURN

*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2),
@.OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.NewCategoryID

UPDATE
BP_Categories
SET CatExpended = CatExpended - @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.OldCategoryIDyou need to use OUTPUT parameters to return a value..check BOL (Books On Line ) its a free download from microsoft...check for correct synax and documentation...

hth|||Thank you...though...I found more info later last night after I came across the phrase 'OUTPUT' parameters.

This thread in particular ...
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=399995

My sproc is finished and I've moved on...

Excuting distributed queries in parallel

Hi,

Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:

SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')

I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:

SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..

Any suggestions?

Rgds

Bob

TSQL statement execution is always serial in a batch or module. You can run the statements in parallel by doing one of the following:

1. Use on-demand SQL Agent jobs which contain TSQL task with each distributed query. You can then start each job in your TSQL code and wait for their completion

2. In SQL Server 2005, you can use service broker messaging infrastructure to activate multiple procs in parallel with each one executing a specific query

However, it is not clear if you are returning the results to client or processing on the server side itself. If you are returning the results to client then these techniques will not be efficient because you will have to dump the results into a table and then query it back. So you might as well run 3 different commands from the client code.

Excel Pivot Table for AS2005

I try to use the Excel Pivot Table to retrieve the data from AS2005 cube data. I have install the Microsoft OLE DB Provider for Analsysis Services 9.0", but I get the following error message: Insitialzaiton of the data source failed. However, when I use the SQL Server Management Studio, I can browse the cube data. As a result, I know I have permission the access the cube data. Why the Excel Pivot Table is not working for me?

This schenario is pretty common and should be working. There got to be something wrong with components installed.

Try applying the latest version of AS OLEDB provider from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.