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

Question

Post reply

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

rkordonsky 63916

SSC Eights!

Points: 812
More actions
February 22, 2024 at 5:22 pm

#4383047

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.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

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

rkordonsky 63916 SSC Eights! Points: 812 More actions · Answer 3

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

rkordonsky 63916 SSC Eights! Points: 812 More actions · Answer 4

rkordonsky 63916

SSC Eights!

Points: 812

February 22, 2024 at 5:47 pm

#4383052

I always use SET NOCOUNT ON.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rkordonsky 63916 SSC Eights! Points: 812 More actions · Answer 6

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.

Ed B Hall of Fame Points: 3666 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

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)

rkordonsky 63916 SSC Eights! Points: 812 More actions · Answer 9

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