Why is my code not returning an error message when @@ROWCOUNT=0?

Question

I created the below sp. When I execute it and insert an ID that does not exist, I want an error message to appear. However, the error message does not get printed... Does anyone can help me understand what I'm doing wrong please? Thank you

create procedure campaign_data
@campaign_ID bigint
as

 begin
 select campaignname,totalspend,clicks,impressions,totalspend/clicks as cpc
 from DSP_RawData_Archive
 where @campaign_ID=Campaign_ID
 end

 exec campaign_data 2
 if @@ROWCOUNT=0 print 'Campaign_ID does not exist'

. . . Your `print` should inside Stored Procedure. BTW, `print` is not an error statement command. — Yogesh Sharma, Feb 01 '19 at 11:13
where exactly are you looking for the error message? your code seems to do the right thing but perhaps it does not do what you're expecting. — Salman A, Feb 01 '19 at 11:15
Possible duplicate of [Return error message from stored procedure](https://stackoverflow.com/questions/33277548/return-error-message-from-stored-procedure) — ChrisCarroll, Feb 01 '19 at 11:20
As presented your statement is quite confusing! All of a batch becomes part of the `CREATE PROCEDURE` (`BEGIN` and `END` do not delimit the body). If you literally executed this, the `EXEC` and `IF` would become part of the stored procedure body (and it would run forever, recursively calling itself). I take it that's not how it actually appears and you'd put in a `GO` when executing from Management Studio. — Jeroen Mostert, Feb 01 '19 at 11:22
This has already been answered. if you search for something along the lines of "sql return error message from proc" you will find answers along with theory for multiple solutions that you can test and apply which suits best or based on your findings is the best approach to take. — ChrisCarroll, Feb 01 '19 at 11:36

score 1 · Accepted Answer · answered Feb 01 '19 at 11:19

The PRINT statement will be displayed under the Messages tab on the results window if you are using SSMS and doesn't affect control flow.

Try throwing an error using RAISERROR with high enough severity (the middle parameter), which can affect control flow (jumps to CATCH or stops execution, for example).

IF @@ROWCOUNT = 0 
    RAISERROR('Campaign_ID does not exist', 15, 1)

score 1 · Answer 2 · answered Feb 01 '19 at 11:29

The problem here is the scope of your @@ROWCOUNT. @@ROWCOUNT returns the number of effected rows of the last executed statement in a batch, in this case that from exec campain_data 2 and not that from the first select. Another example:

BEGIN TRAN
SELECT 0 WHERE 1 = 0

PRINT @@ROWCOUNT -- Displays 0

EXEC dbo.DoSomething -- Say this procedure returns 2 rows...

PRINT @@ROWCOUNT -- Displays 2
COMMIT

Another thing here is that you maybe want to show a proper error message in your scenario (instead of a simple PRINTed line). You can do achieve this using either

RAISERROR('Display my custom error message via RAISERROR!',16,1)

or

THROW 50000,'Display my custom error message via THROW!',1

Helpful article: http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/

score 0 · Answer 3 · answered Feb 01 '19 at 11:17

Try :

create procedure campaign_data
@campaign_ID bigint
as

 begin
 select campaignname,totalspend,clicks,impressions,totalspend/clicks as cpc
 from DSP_RawData_Archive
 where @campaign_ID=Campaign_ID
 end

 exec campaign_data 2
 IF (SELECT @@rowcount) = 0
        SELECT 'Campaign_ID does not exist'

score 0 · Answer 4 · answered Feb 01 '19 at 11:44

0

I tried your stored procedure in Sequel Server Management Studio, and I saw the right response in message tab.

Where are you trying it?

answered Feb 01 '19 at 11:44

Emanuele Leoni

168
1
12

Why is my code not returning an error message when @@ROWCOUNT=0?

4 Answers4