How to get the print message in sproc

Question

Post reply

How to get the print message in sproc

bto310

SSC Rookie

Points: 30
More actions
December 3, 2008 at 12:47 am

#216034

I'm doing a application integrated with other apps, at one point I could only invoke the stored procedure in database that the other app provided, in that sproc there'll be several case of failure, and I should retrieve the execute result of that sproc and know why they failed in case of failure.
The sproc is like this：
if @search_type <> 'ausId' and @search_type <> 'email'
begin
print 'Search Type must be either ''ausId'' or ''email'''
set @return_code = 0
GOTO ERR_HANDLER
end
I can get the @return_code variable, to know whether the operation succeed or failed.
But how can I get that print message of "Search Type must be either ''ausId'' or ''email''"?
That is very important as it indicates the reason why it fails.
I'm using JPA of EJB, creating native query to invoke the procedure. In face it's similar to JDBC call.
Anyone has suggestion on it?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try RAISERROR instead of print.

if @search_type <> 'ausId' and @search_type <> 'email'

begin

RAISERROR ('Search Type must be either ''ausId'' or ''email''', 16,1)

set @return_code = 0

GOTO ERR_HANDLER

end

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 2

You can also add the WITH NOWAIT to the RAISERROR statement.

N 56°04'39.16"
E 12°55'05.25"

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Is this 2005?

If so... GOTO? Oooh, yuck. 😉

Using TRY/CATCH you can then do as Gail suggested and fire off RAISERROR which will automatically move you into the CATCH statement. Much more clean and easy to maintain.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

bto310 SSC Rookie Points: 30 More actions · Answer 4

Thanks guys, it's really helpful.

But is there anyway that I can get the message without changing the sproc?

Since the sproc has already been there for a while, and it's not only used by me, so the developer may not change that for me.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It should be possible, but I don't know enough about Java to know how. Perhaps ask that on a java forum?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass