Handling Errors in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Handling Errors in Stored Procedure

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-05-22 : 15:05:59

Greetings,

I have searched the forum for the particular problem I'm facing, but didn't find what I've been looking for.

I have a stored procedure:

USE [Dynamic_Glossary]
GO

/****** Object:  StoredProcedure [dbo].[Output_Data]    Script Date: 05/22/2012 09:47:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[Output_Data]
(
	@ColumnNumber  sysname,
	@language varchar (30)
	
	
)
AS
SET NOCOUNT ON;



BEGIN
TRY

EXEC ('SELECT English_Words,  Word_Def as Definition, '+@language+' as "Translation To" FROM [Languages] WHERE [' + @ColumnNumber + '] = 1')




END TRY

BEGIN CATCH
PRINT 'Your Language is not supoorted'
end catch

And what I'm trying to do is to show a message "The current language is not supported"

I have 11 columns with different languages in a table, so what I want is if the input doesn't match the Column Name it shows that error message.

I just recently added the TRY and CATCH, it doesn't throw an error anymore, but it doesn't print the catch statement, it returns 0.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 15:32:39

I cannot repro you issue, maybe try adding this select to your catach block to see if it returns anything:

	
SELECT
      ERROR_NUMBER() AS ErrorNumber
      ,ERROR_SEVERITY() AS ErrorSeverity
      ,ERROR_STATE() AS ErrorState
      ,ERROR_PROCEDURE() AS ErrorProcedure
      ,ERROR_LINE() AS ErrorLine
      ,ERROR_MESSAGE() AS ErrorMessage;

It's possible that someting is generating an error severity that a catch block won't trap. Also, is there any chance you can change your schema to a more normalized one?

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-05-22 : 15:44:23

Thank you for quick reply.

I have that actually in my code I was just testing it. The error I get is

 ErrorMessage
Invalid column name 'sdf'.

So what I want is instead of "invalid column name" it prints a different message "Language is not supported.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 17:13:31

I'm not sure how you are testing your code, but from SSMS I get a printed message in the Messages tab.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-22 : 17:13:55

Perhaps you want to use the RAISERROR function?

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-05-22 : 17:43:38

I might explaining this wrong. Let me try this once more.

So when I execute the above procedure it runs perfectly. And I do get the message saying "Your Language is not supported". But that's in the Message tab. In the result tab, I get Return Value 0. What I want is so somehow that message showed in the result tab, because later I'm using this procedure in C#. And when I run my C# code and if the columnName doesn't match it shows a blank page. And instead of that I want to print "Language is not supported".

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-05-24 : 17:59:25

Any help?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-05-25 : 03:02:35

I'd start again.
You should not need to use dynamic SQL to do a simple translation lookup. Redesign your database so you don't have to do that.
As Lamprey says - raise an actual error and deal with that in your client code.
If you really insist then you have to do

select 'The current language is not supported'

to get your message out in the results.

But you really want to look at the design - it's not correct.

SQL Basic
Starting Member

8 Posts

Posted - 2012-05-25 : 05:43:59

Is there nothing script code that mistake in your structure?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-25 : 11:33:22

quote:
Originally posted by burzum

I might explaining this wrong. Let me try this once more.

So when I execute the above procedure it runs perfectly. And I do get the message saying "Your Language is not supported". But that's in the Message tab. In the result tab, I get Return Value 0. What I want is so somehow that message showed in the result tab, because later I'm using this procedure in C#. And when I run my C# code and if the columnName doesn't match it shows a blank page. And instead of that I want to print "Language is not supported".

As LoztInSpace mentioned, just PRINTing from SQL will not do anything to your application or web page. You need to return the string either via a SELECT, and OUTPUT parameter or using RAISERROR (as an error or warning). Then deal with that result in your calling environment.

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-05-30 : 13:55:39

Thank you, that helped.

However, there's one problem I'm still struggling with.

What I want want is if the Column Name doesn't match it should print an output. So far it does that in SQL. But I'm using this stored procedure in C#. So now, when I run it and if the column name doesn't match it shows an error, instead of showing me that message. Any ideas on how to display that message in C#.

That is the error:

"Cannot find column [English_Words]."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources