Basing Report on a Procedure with Cursor

I need some advice on how to design a report using cursors.

Is it possible to base a report on a cursor?

I would think that I would have to put the cursor into a stored procedure and call that procedure. But if I do use the cursor in the procedure, will the looped variables automatically populate the report or will I need to insert them into a table that populates the report?

Thanks,

cj

If the data source type is "Oracle" and you want to call a Oracle stored procedure, please search for related threads on this forum regarding Oracle stored procedures (e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1).

-- Robert

|||No, it is SQL Server.|||

This should work just fine if you follow the guidelines in http://msdn2.microsoft.com/en-us/library/ms159260.aspx - scroll to the stored procedure section.

If you experience problems, can you post a sample of a SQL stored procedure that doesn't work?

-- Robert

|||

Thanks, I understand that part now. Usually I am populating a table to get my output from the stored procedure. For example, I'll use the INSERT clause to populate as the cursor loops. Can I populate the dataset instead? If so, how?

Thanks,

cj

|||

You could use temp tables in the stored procedure. For example:

CREATE PROCEDURE MyProc
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT * FROM #t
GO

However, in that case make sure to use the text-based query designer (with 2 panes) and keep in mind that automatically generating a schema (getting the list of fields) fails on SPs with temp tables. This automatic method is invoked when you switch from Data view to Layout view.
Instead, before going to Layout, get the schema manually by clicking on the Refresh Fields button which will manually refresh the query.

The automatic method gets a schema quickly when available without any necessary user interaction. (Behind the scenes it is running the query with SET FMTONLY ON). The manual method gets the schema by running the query. It takes longer and will prompt the user for query parameter values if required. The former works for most queries with the exceptions of temp table use or some dynamic SQL. The latter always works, but has performance drawbacks for some queries.

-- Robert