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?


  • 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.


  • 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 10 (of 10 total)

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