Stored Proc w/Parms as report recordsource

[Brian Schwartz]

We have a stored procedure which takes several paramters. We want to make it
the record source for a report.

Searching through a couple newsgroups, I've seen how to dynamically modify
the querydef of a pass-through query to set a stored proc's parameters at
run time--and that works okay for us--and it appears that an ADP does
something to facilitate this. But is there a way to call a stored proc
directly in a SELECT statement from code?

For example, it'd be great if we could do something like this:
SELECT field1, field2 FROM stored_proc(parm1, parm2, parm3)

Thanks,
Brian Schwartz

 

[Sylvain Lafontaine]

No, you must use an OPENQUER, an OPENDATAROWSET or OPENDATASOURCE.

Some stored procedures might be replaced with User Defined Procedures (UDF)
if they are not too complex, so you should take a look at UDF.

 

[Brian Schwartz]

User-defined functions look cool--I never knew you could do that. But it
appears that they can only be used to fill combos & lists--is that true? We
need to make it the source of a report.

Thanks,
Brian Schwartz

 

[Sylvain Lafontaine]

You can enclose them in SP or use the syntax « select * from dbo.MyUDF
(....) » as the source of a report.