MS SQL Server Stored Procedures 
Author Message
 MS SQL Server Stored Procedures

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



Sun, 25 Jun 2000 03:00:00 GMT  
 MS SQL Server Stored Procedures

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



Sun, 25 Jun 2000 03:00:00 GMT  
 MS SQL Server Stored Procedures

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

Quote:

>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



Sun, 25 Jun 2000 03:00:00 GMT  
 MS SQL Server Stored Procedures

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.



Tue, 27 Jun 2000 03:00:00 GMT  
 MS SQL Server Stored Procedures

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.).

Quote:

> 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.

--



Tue, 27 Jun 2000 03:00:00 GMT  
 MS SQL Server Stored Procedures

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.  



Quote:
> 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



Sun, 02 Jul 2000 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Calling a SQL Server 2000 store procedure from MS-Access

2. MS SQL Server BLOBS, RDO and Stored Procedures

3. MS SQL Server 6.0 stored procedures

4. MS Sql Server 6.0 Stored Procedures

5. doing updating from grid to sql server with stored procedure sql 2000

6. Executing MS SQL Server 2000 stored procs via MS Access 2000

7. ADO, DataGrid, MS SQL, stored procedure

8. Using MS SQL Stored Procedures with VB5

9. CR7 with Stored Procedure in MS SQL Server7.0

10. Pass parameter to a MS SQL stored procedure from Crystal Report 6.0

11. MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

12. SQL Server 7 stored procedures

 

 
Powered by phpBB® Forum Software