Stored procedure... to crystal

[Fursten]

Hi,

I have a report based on a stored procedure something like this:

CREATE PROCEDURE [dbo].[Teste]

@cod as varchar(3)
AS
Select * FROM Table WHERE field_cod = @cod
Go

Running this stored procedure, the result will be a set of records...

However, I will need to make an update to some of the records that result of this select, and just after that I will want to send the records(now changed) to the client(crystal).

So, I will need to declare a variable of cursor type... I think, that will hold me the records while I´m changing them.

The problem is that, how can I say to SQL Server that after I made the changes, the output of my stored procedure should be those records (with the changes in them)?

Thank you in advance.

Sérgio Oliveira

 

[kenhamady]

This isn't really a 'formula' question, but if you are writing the changes back to the table, can't you just run another selectc from the table to pick up the changes? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[balves]

The last SELECT statement of the stored procedure is the recordset to Crystal. What you do before that with cursors, selects, updates, whatever, is not passed back to Crystal. Crystal only gets the last SELECT of the proc.

 

[Fursten]

Yes, you are both wright in what you are saying but I didn´t explain myself very well... The problem is that by using a variable of cursor type, I would like to be able to navigate through the records and to change some fields in some records... but I don´t want that changes to be reflected in the database.

I thought I could create some kind of static cursor that would able me to update that cursor in memory and not in the table itself. It should be something like having a static cursor on the "client side" (visual basic terminology...), however, of course, I want that everything to be done in the server...

After I change that "in memory recordset", I would try to output it from the stored procedure.

Maybe this as not any sense... but I would apreciate some opinions aout this... Is it possible?

PS: Maybe this is not the best discussion group to put this question... because is more a transact sql question than Crystal question.. but I just realise that now.

Sérgio Oliveira

 

[kenhamady]

Yes, that is really a SQL question. However, the answer has to take into account that CR will need a table from somewhere as a final result, either a recordset in memory or a temp table on the disk. Could you write the changes to a temp table and then do a select from it? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Fursten]

Yes, I thought on that. Maybe that is the only way.

Thank you

 

[balves]

That's the way to do it.

Create a temporary table at the beginning of the proc, say #T1, populate it and update it any way you want, and finish the stored proc with SELECT * FROM #T1.