none
Basing Report on a Procedure with Cursor RRS feed

  • Question

  • 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

    Friday, June 2, 2006 7:04 PM

Answers

  • 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

    Saturday, June 3, 2006 7:25 PM
  • 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

    Wednesday, June 7, 2006 5:23 AM

All replies

  • 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

    Friday, June 2, 2006 8:06 PM
  • No, it is SQL Server.
    Friday, June 2, 2006 8:24 PM
  • 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

    Saturday, June 3, 2006 7:25 PM
  • 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

     

     

    Sunday, June 4, 2006 12:59 AM
  • 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

    Wednesday, June 7, 2006 5:23 AM