Post reply

Supress intermediate results from SP: SQL 2K

  • November 1, 2002 at 5:32 am

    #79851

    Hi,

    I am working on SQL Server 2000.

    I have a stored procedure in which I do multiple intermediate fetches for further computations. When I get data in the dataset I get the results of all these intermediate fetches. I just want to have the final output from the stored procedure and need to supress all other intermediate fetches. Can I do this? Is there any way in which I can supress these intermediate query results?

    Any help on this will be really helpful.

    Thanks in advance.

    Ashish

  • November 1, 2002 at 5:38 am

    #440039

    What exactly are you getting is it the results of a select statement or the number of rows affected? If number of rows place SET NOCOUNT ON at the begining of your SP after the AS of the CREATE PROC area, and if you need for some reason to have the numbers on the final results do SET NOCOUNT OFF right before that final query.

  • November 1, 2002 at 5:42 am

    #440041

    Hi,

    I have already set SET NOCOUNT ON. But what I am gettting is the result of SELECT statements. I have to fetch some records for intermediate computations and need to suppress these intermediate FETCH results. I just need the output of the final SELECT statement.

    Thanks in advance

    Ashish

  • November 1, 2002 at 6:12 am

    #440042

    See if all intermediate results can not be inserted into temporary tables so that the only select statement is the final one. There is a way to cycle through the results in you result set but exactly how escapes me now.

  • November 1, 2002 at 6:20 am

    #440043

    If you FECTH them into a cursor, variable or temp table object the rows should not output just the number of rows affected. Can you post an example of the code so we can look at your logic and maybe where this is being caused.

  • November 2, 2002 at 6:35 am

    #440134

    any select statement that does not return the results into a variable or table will be returned as a rowset to the calling code. With ado these can be cycled through using .nextrecordset.

    As has been said, if you dont want these make sure you are storing the results of select statements in variables or tables.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply