stored procedures in crystal

[mirage10]

crystal reports 11 accessing Stored procedures in sql server 2000
Pl. bear with me as this is the first time I am
dealing with SPs.

I created a very basic stored procedure in sql server:

CREATE PROCEDURE proc1 @ITEMID varchar(40) AS
SELECT MIITEM.ID,MIITEM.DESCR FROM MIITEM WHERE ID =
@ITEMID
GO

now i access this stored procedure through crystal v11 and
I am able to get the data I was expecting.

acc. to theory stored procedures donot return data and
functions return data.

in crystal help it says that ,in oracle we need to use
"REF cursor" which enables to get the data from the
stored procedure, but here i did not use any thing
like ref cursor in sql server, still i was able to get
all the data.

pl. help me understand the concept. thanks,

------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[vidru]

acc. to theory stored procedures donot return data and
functions return data.

Who's theory? Sounds like you've been given some bogus information.

Stored procedures can be used for many things. They don't necessarily always return recordsets, but they certainly can (we use stored procs for 90% of our reports). Functions also can be used for many different things. I suggest you have a look at SQL Server Books On Line (aka the SQL Server help files) to get a better understanding of what each can/can't do.

Oracle and SQL Server are two very different animals, hence the different requirements for using stored procs for each with CR.

-dave

 

[mirage10]

from expertsexchange.com

they say that

comment:
Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not.

reply to comment:
SPs can only return an INT value. You can return rowsets, tables, VARCHARs, INTs, and other data types with a FUNCTION.

from sql server help file i see
"Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression"

i understood "they cannot be used directly in an expression" part but everything else seems to be vague to me.

there is an ocean of material on the internet, but everywhere i see vague information like above.Pl. help me understand this concept.
also, pl. advise which study material i should use to start understanding the concepts of stored procedures
thanks


------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[mirage10]

Can you send me links to some samples which show how SPs are used in crystal reports?sql server or oracle?

------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[vidru]

Can you send me links to some samples which show how SPs are used in crystal reports?sql server or oracle?

Well, for SQL Server, you've already done it (albeit as basic as you can get). As for Oracle, haven't used it with CR, but this document demonstrates how the procedure needs to be written for you to be able to use it with CR.

For your general questions about stored procs/functions/etc., try going through the FAQ section of forum183 (Microsoft SQL Server: Programming).

Based on the comments you received (in your second post), I can only assume that they were trying to say that stored procedures by default have a single return value, which is typically used to report success or failure of the procedure's execution. That is completely different from writing a stored procedure to produce a recordset to populate a report.

-dave

 

[JasonL99]

A stored procedure always returns a return code which is an int. It can also optionally return 0,1, or many resultsets and output parameters.

I am using Crystal 8.5 and as far as I know a report can use the first resultset returned from a stored procedure but cannot use any other resultsets or return codes or output parameters.