How To use a stored procedure in a crystal reports 8.5

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.

There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons.

• Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.
• The Iterative Method. Pass a delimited string and loop through it.
• Using the CLR. SQL Server 2005 and higher from .NET languages only.
• XML. Very good for inserting many rows; may be overkill for SELECTs.
• Table of Numbers. Higher performance/complexity than simple iterative method.
• Fixed-length Elements. Fixed length improves speed over the delimited string
• Function of Numbers. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.
• Recursive Common Table Expression (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.
• Dynamic SQL. Can be slow and has security implications.
• Passing the List as Many Parameters. Tedious and error prone, but simple.
• Really Slow Methods. Methods that uses charindex, patindex or LIKE.

I really can't recommend enough to read the article to learn about the tradeoffs among all these options.

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable