I  currently use a stored procedure with a report, that performs a simple
select

e.g.

select a, b, c from a_table

This allows me to select a, b and c as fields to use in my report but if I
want to perform more complex queries using cursors I don't seem to be able
to make the fields available. How can I do this. ?

Can someone post an example of a SQL Server stored procedure that uses
cursors and works with Crystal Reports.

Thanks

Andrew Briggs

What do you want to do. You can build  a cursor with "Create Table" and
fill it up with any data that you want using Insert, Update, etc. and
return the data to CR with a select against the data.
 I have attached a store proc that I have used. It is targeted to Sybase
Sql server which is a subset of MS Sql server. This means that it should
work in MS Sqlserve but MS Sql Server can do more and more eleganly.

begin 600 61.Sql
<encoded_portion_removed>
end

Here is a quick sample:

use tempdb
go
drop procedure test
go
create procedure test
as


create table #temp
(dbid smallint,
 name varchar(30))
declare junk cursor for select dbid,name from master..sysdatabases
open junk


 begin


 end
close junk
deallocate junk
select * from #temp
go
exec test
go

Thanks for that.

Am I correct in thinking from this example that you can only have one select
statement or set of results in a stored procedure used by Crystal Reports ?

I'd also like to have multiple select statements from different tables that
are unrelated, in a procedure.

Using

select...

select...

select...

only makes the fields from the first statement available.

Will I have to build a temporary table that contains all the required
fields. This could be difficult as there may be a one to many relationship
with some of the data. E.g. My report could have a standard header that only
requires one record from the database but the details section could contain
several records.

That's exactly what we've had to do - combine several related result sets
into a single set and then format it using Crystal (see my posting last Wed.).

There seem to be a lot of complicated answers to your original question.
However, all I did was insert one line of code into my stored procedure (at
the front) and then Crystal could see it fine regardless of cursors:

set nocount on

Hope this works for you.