Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: suppess resultset in stored procedure ?

    I am try to execute a stored procedure via HTML using an XML template. The last statement in the procedure is a select stmt with FOR XML specified which will provide the output back to the calling program.
    The problem is, the stored procedure I am calling in turn calls other procedures, which may also provide result sets.
    So, I get this error message back
    <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="Streaming not supported over multiple column result" ?>

    I would really like a way to suppress all the resultsets produced during the execution of the procedure except the last one. Is there a way to do that within a procedure?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is not uncommon for a stored procedure to call other stored procedures, but it is rare and of questionable value to nest a stored procedure that returns a result set rather than a simple output variable.

    Are you sure this is going on? I think it is more likely that your stored procedures don't have "SET NOCOUNT ON" declared, and thus a just returning intermediary rowcounts from processing, and this is what is confusing your application.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    The nested proecedures do indeed return result sets. Some of them are used by various programs that expect a result set. I can add a flag to the procedures to suppress the result sets, but I was hoping to not have to.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Procedures should not call other procedures that produce result sets (except shell procedures used to optimize caching). The calling procedure has no easy way to use the result set. You should have separate procedures, or use user-defined table functions, or as a last resort add a ForOutput parameter as you have mentioned. I'd have to question how well the app was originally designed...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    create a temp table in your stored procedure
    insert into #temp exec your_sp
    -----------------
    KH


  6. #6
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    The result sets generally contain debug output. Contents of status tables and whatnot during execution. They do not return information useful to calling programs, but rather information useful to a human manually running the procedures to trace through them. It was never a problem before because the calling programs can ignore the result sets.

    I'll give your suggestion a try khtan. Thanks.

Posting Permissions

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