How to suppress the info of queries execution in Stored Procedure.

  • I inherited the Stored procedure the whole purpose of it is to populate our catalog table by data from multiple tables. I had to create a Cursor to update the data in the table. When I run the stored procedure, I see the result of multiple queries even the code is very simple: Insert into Table1 Select field1, Field2, etc from Table2. I need the stored procedure only shows when the stored procedure completed. Any suggestions? Thank you.

  • rkordonsky 63916 wrote:

    When I run the stored procedure, I see the result of multiple queries ...

    You mean, the data? Where, exactly, are you 'seeing' this?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • likely you need "set nocount on" at the top of your SP (it should be in ALL of them really)

  • In SQL Management Studio Query windows SQLQuery#.sql that was open when the stored procedure was executed.

  • I always use SET NOCOUNT ON.

  • rkordonsky 63916 wrote:

    I always use SET NOCOUNT ON.

    Then you must have SELECT statements in your proc.

    INSERT ... SELECT

    does not output the selected data to the SSMS window.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • There is a cursor in the SP and in it there is this code:

    Select * from dbo.web_Online_Library_Products where RIGHT(PRODUCT_CODE, 3) = 'WFM' and SOURCE_NUMBER = @SN and CHAPTER_NUMBER = @CN

    if @@ROWCOUNT > 0

    Is there any way to suppress the result of query. Based on the @@ROWCOUNT it did update one way or another.

  • It looks like it runs the query to see if any data will be returned in order to make @@ROWCOUNT >0. Either set a variable to the count, or just check whether data exists.

    -- This runs the query and if it returns any data @@rowcount is > 0
    Select * from dbo.web_Online_Library_Products where RIGHT(PRODUCT_CODE, 3) = 'WFM' and SOURCE_NUMBER = @SN and CHAPTER_NUMBER = @CN
    if @@ROWCOUNT > 0

    -- Change it to this unless you need the actual row countt
    IF EXISTS (Select * from dbo.web_Online_Library_Products where RIGHT(PRODUCT_CODE, 3) = 'WFM' and SOURCE_NUMBER = @SN and CHAPTER_NUMBER = @CN)
    BEGIN

    END
  • rkordonsky 63916 wrote:

    I had to create a Cursor to update the data in the table.

    It would be interesting to know more about that.  Usually, it's a serious mistake that can be totally and fairly easily avoided.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you very much! That is what I was looking for. I do appreciate your help.

Viewing 10 posts - 1 through 9 (of 9 total)

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