view vs. function vs. procedure - really basic question

[Rank Novice]

I'm a developer, not a DB admin. I'm writing a .NET app that uses
crystal reports.

The table I need to output is built inside a stored procedure. No
choice, it makes use of some temporary tables. (Believe me I've tried
to get around that.)

Crystal reports seems to only know about tables and views. It looks
like a view cannot call procedures. It can call functions, but in turn
they also can't call procedures. I am hosed, what now?

Performance is not a factor here, small data sets, I just gotta get the
thing working.

[David Portas]

Re: view vs. function vs. procedure - really basic question

It's a long time since I used Crystal. Are you certain it can't call a proc?
It might be worth seeking some help on the Crystal side first.

A function can't call a proc but I expect you can rewrite your proc as a
function or a view. Contrary to what it might seem there are usually ways to
do things without temp tables and if not you can often use table variables
instead. If you need more help, please post DDL, sample data and required
results: http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: view vs. function vs. procedure - really basic question

Rank Novice (pnwms@yahoo.co m) writes:[color=blue]
> I'm a developer, not a DB admin. I'm writing a .NET app that uses
> crystal reports.
>
> The table I need to output is built inside a stored procedure. No
> choice, it makes use of some temporary tables. (Believe me I've tried
> to get around that.)
>
> Crystal reports seems to only know about tables and views. It looks
> like a view cannot call procedures. It can call functions, but in turn
> they also can't call procedures. I am hosed, what now?
>
> Performance is not a factor here, small data sets, I just gotta get the
> thing working.[/color]

We use Crystal a lot in our shop. And stored procedures with a lot of
temp tables.

Our Crystal does not even now that there is an SQL Server database at
the other end. The "database" we present for Crystal is a TTX file, which
defines the fields for the report. We feed Crystal the data it needs
by passing it ADO recordsets (we are still on VB6). While you can use
ADO recordsets from .Net with the OleDb .Net data provider, this is not
something would like to do. I don't know, but I would expect Crystal to
be table to be fed a data set as well.

As David said, asking in a forum for Crystal might be a better strategy.
I know that Business Objects (or whatever the company is called) has
web forums on their web site.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rank Novice]

Re: view vs. function vs. procedure - really basic question

>As David said, asking in a forum for Crystal might be a better strategy.

I'll check it out. The businessobjects .com website is down for
maintenence right now, which is why I'm here, and I've got to get this
out by Monday!

[Rank Novice]

Re: view vs. function vs. procedure - really basic question

>It's a long time since I used Crystal. Are you certain it can't call a proc?

I am way out of my element here and certain of nothing.
[color=blue]
>A function can't call a proc but I expect you can rewrite your proc as a
>function or a view. Contrary to what it might seem there are usually ways to
>do things without temp tables and if not you can often use table variables
>instead.[/color]

There may actually be some sneaky SQL trick here, if there is I'd much
rather be calling a function. I'll be back with actual code.

For now the workaround is to have the procedure generate a table and
use that for the report. That rubs me the wrong way but it'll get me
through the single-user demo anyway.

[John Bell]

Re: view vs. function vs. procedure - really basic question

This looked useful!

http://tinyurl.com/9n9jx

"Rank Novice" <pnwms@yahoo.co m> wrote in message
news:1122230133 .816562.312780@ g44g2000cwa.goo glegroups.com.. .[color=blue][color=green]
> >It's a long time since I used Crystal. Are you certain it can't call a
> >proc?[/color]
>
> I am way out of my element here and certain of nothing.
>[color=green]
>>A function can't call a proc but I expect you can rewrite your proc as a
>>function or a view. Contrary to what it might seem there are usually ways
>>to
>>do things without temp tables and if not you can often use table variables
>>instead.[/color]
>
> There may actually be some sneaky SQL trick here, if there is I'd much
> rather be calling a function. I'll be back with actual code.
>
> For now the workaround is to have the procedure generate a table and
> use that for the report. That rubs me the wrong way but it'll get me
> through the single-user demo anyway.
>[/color]