Help on Stored Procedure

[pritiw]

Hi,
I have to report from a stored procedure...
the basic stored procedure is
The DB is SQL SERVER
Open a cursor
Step through the Cursor
put the values selected into a variable
and for some condition calculate another variable
(Set @AmountPayable = @AmountPayable + @AmountDue)
and finally insert one record into a table

insert into AccPayable(Description,AmountPayable) values)
select * from AccPayable

After that i have to report from the table to which i have inserted the data.
But the problem is when i select the stored procedure it returns fields from the cursor which i opened in the beginning of the stored procedure......

is it possible to report like this

Thanx in advance for any help

 

[olichap]

At the beginning of your stored proc you must be doing a select statement and passing that resultset to the cursor. It sounds like your problem relates to having more than one select statement in a stored proc.

If I first to a Select * from Customers, then later have Select * from Invoices in the same proc SQL Server will only report off of the first resultset.

In your first query create a temp table like so:
Select *
INTO #TempTable1
from Customers where custid = @ID

Base your cursor on the temp table. I'm not too clear on what steps you are following between the cursor and your insert statement. I assume you would then use values from #TempTable1 in your insert statement.

If you select INTO (create the temp table) first, you do not have conflicting "Select" statements later on. With some variation of what I have here you should be able to return the information you want. Let me know if this is unclear or sounds incomplete (or if I am just way off in my assumption of how your proc is setup).

Oliver