Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    60

    Unanswered: Using Procs inside a Proc

    Question: Is there a way to execute a stored procedure directly into a cursor? Right now I'm inserting into a temp table and then using a cursor to loop through it. For example:

    --Look up the parameter types of the stored procedure
    INSERT INTO #parms
    EXEC ('sp_sproc_columns '+@sp_name)

    --Loop through #parms table with cursor

    It seems inefficient to me plus there's another problem. The results of the procedures may exceed the max row size and I get errors which I haven't figured out how to supress:

    The total row size (9870) for table '#parms' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

    Thanks for any assistance you can provide.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    bill_dev,

    If you are patient, a guy named rdjabarov will log on and tell you that this can be done using OPENROWSET.

    As for me, I'd open up sp_sproc_columns, pirate the code I want, and turn it into a user-defined table function. The results can then be used in Insert statements, cursors, or whatever you want.

    And the error your getting is because the sum of the varchar column widths in #parms (plus any other columns) exceeds the maximum size of a record. Since varchars only take up as much space as the information they hold, this won't be a problem unless you try to stuff too much data in a record.

    For good measure, you should redefine them to the smallest lengths necessary to hold your data.

    blindman
    Last edited by blindman; 09-06-03 at 01:22.

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Using procs inside of procs is an excellent way to have optimized execution plans, using procs inside cursors are not the best idea depedning on the variance of your params. Best advice would be to see if you can eliminate that cursor.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Sep 2003
    Posts
    60
    Originally posted by rhigdon
    Using procs inside of procs is an excellent way to have optimized execution plans, using procs inside cursors are not the best idea depedning on the variance of your params. Best advice would be to see if you can eliminate that cursor.

    HTH
    Thanks, guys. I was able to remove the warnings by pirating then writing my own Usp_Sproc_Columns. I'm using the cursor to pivot the data so the results are one row per procedure and its parms but maybe I'm overlooking an easier way - then again that may be another thread. Thanks again.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OPENROWSET? Possibly, but not the best approach for this situation. I think you made the best suggestion (bm), but I would definitely remove cursor.

  6. #6
    Join Date
    Sep 2003
    Posts
    60
    Originally posted by rdjabarov
    OPENROWSET? Possibly, but not the best approach for this situation. I think you made the best suggestion (bm), but I would definitely remove cursor.
    Okay but I'd need to pivot the dynamic columns of this:

    Proc_name Col_name Col_type Col_direction
    uspGetData columnA varchar(100) Input
    uspGetData columnB int Input

    ...into one row per proc like this without using cursors (I don't need actual columns. They can be concat'd like this):

    Proc_name Columns Types Directions
    uspGetData 'columnA,columnB' 'varchar(100),int' 'Input,Input'

    I know all the gurus say cursors are BAD but I haven't found a better way. And can I assume from ya'll comments that syntax doesn't exist to put stored procedure output straight into a cursor (even though it is inefficient)? Thx
    Last edited by bill_dev; 09-07-03 at 02:25.

  7. #7
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    BM's advice of using a UDF to output to a cursor would work fine.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cursors bad?

    Let's get this straight. SQL Programmers shun cursors because they are not as efficient as set-based processing, around which the SQL Server engine is designed. And also, probably, because they are frequently over-used by novice SQL programmers who have prior experience in procedural programming languages, and are not yet adept at thinking about operations on entire data sets.

    But that doesn't mean they are "bad".

    On small data-sets, their ineffeciency is not a big consideration, and there are some occasions when they are definitely the best solution for a problem.

    If your cursor solution works, and blazing speed is not a consideration, then what matters is accurate output, and the readability of the code.

    blindman

  9. #9
    Join Date
    Sep 2003
    Posts
    60
    Originally posted by blindman
    Cursors bad?

    Let's get this straight. SQL Programmers shun cursors because they are not as efficient as set-based processing, around which the SQL Server engine is designed. And also, probably, because they are frequently over-used by novice SQL programmers who have prior experience in procedural programming languages, and are not yet adept at thinking about operations on entire data sets.

    :

    blindman
    ...that's why I asked you guys.

    Thanks again. Here's one place where I got it working but I haven't finished the main example (w/pivot) that I mentioned earlier:
    http://billupton.com/dotnetgen.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •