CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Satyaprakash J
Satyaprakash J

Reputation: 121

Suppress error message in SQL Server 2008

I have the following code. When executed it prints

Msg 8120, Level 16, State 1, Procedure Test, Line 17
Column '@t.Country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ERROR'

But I want only 'ERROR' to be printed. Is there a way to suppress the error message?

Thanks Satyaprakash J

CREATE PROCEDURE Test
AS
    DECLARE @t TABLE
        (ID INT IDENTITY(1,1)
        ,Country NVARCHAR(50)
        ,Year INT
        ,PopulationInMillions INT
        )

    INSERT @t (Country, Year, PopulationInMillions)
    VALUES ('US', 2000, 20),
           ('US', 2001, 22) 

    SELECT Country, MAX(PopulationInMillions)
    FROM @t       
GO

BEGIN TRY
    EXEC Test
END TRY
BEGIN CATCH
    PRINT 'ERROR'
END CATCH

Upvotes: 1

Views: 13750

Answers (4)

joegomain
joegomain

Reputation: 864

There are two issues here.

The main issue is, as Martin Smith pointed out, your error is coming from the create procedure statement. Not from exec. To elaborate further, it is a Parsing and Compilation error indicating you cannot select a column and an aggregate function with out group by(ing) it. Do this.

SELECT Country, MAX(PopulationInMillions)
    FROM @t
    GROUP BY Country --add this line

but even if you (try to) do

begin try
    DECLARE @t TABLE
        (ID INT IDENTITY(1,1)
        ,Country NVARCHAR(50)
        ,Year INT
        ,PopulationInMillions INT
        )

    INSERT @t (Country, Year, PopulationInMillions)
    VALUES ('US', 2000, 20),
           ('US', 2001, 22) 

    print N'Before error';

    SELECT Country, MAX(PopulationInMillions)
    FROM @t
    --GROUP BY Country    --comment in attempt to cause error

    print N'After error';

end try
begin catch
    print N'Caught error';
end catch

You won't even get to print N'Before error'; Because the error Msg 8120 is a fatal error returned at parse and compile time. The whole batch is not even executed.

Here's a good article on understanding how queries are processed http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

And here's a good article from the times there weren't TRY...CATCH yet. But a good source for understanding fatal/non-fatal sql errors. http://www.sqlteam.com/article/handling-errors-in-stored-procedures

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

If the error actually occurs inside of the TRY block, then yes, you can - exactly as you've shown:

BEGIN TRY
    RAISERROR('Hello!',16,1)
END TRY
BEGIN CATCH
    PRINT 'ERROR'
END CATCH

Just prints ERROR. Similarly:

create procedure P
AS
    RAISERROR('World!',16,1)
GO
BEGIN TRY
    EXEC P
END TRY
BEGIN CATCH
    PRINT 'ERROR'
END CATCH

Also just prints ERROR.

But, as others have tried to tell you, it doesn't matter how you structure your TRY/CATCH if the error is occurring before they're ever entered. And there's no simple way to create a procedure within a TRY/CATCH.

Upvotes: 1

John Fraser
John Fraser

Reputation: 58

Fix the stored procedure creation statement as above is the only way you can suppress the error message.

The create procedure statement is raising the error due to the syntax being wrong. The stored procedure is never created in the database. The begin try is not executing a stored procedure as the procedure does not exist. You could put EXEC {anything} in the try to get the text error displayed.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Is there a way to suppress the error message?

Yes, there is.

Fix your query:

SELECT Country, MAX(PopulationInMillions)
FROM @t

Like so:

  • Add GROUP BY country like so:

    SELECT Country, MAX(PopulationInMillions)
FROM @t 
GROUP BY Country;

    Or,

  • Use an aggregate function with Country:

    SELECT Max(Country), MAX(PopulationInMillions)
FROM @t

    Or,

  • Remove the Country field from the SELECT list:

    SELECT MAX(PopulationInMillions)
FROM @t

I think you need to read more about how the GROUP BY clause works. It is crucial to understand how grouping works in SQL.

Upvotes: 0

Related Questions