Doubt

CREATE PROCEDURE emp_ins @.no integer,@.name varchar(20),@.sa integer,@.msg
varchar(20)output AS
if @.no<0
BEGIN
set @.msg='Empno>0'
RAISEERROR(@.msg,10,1)
end
insert into emp values(@.no,@.name,@.sa)

my requirement is i want to implement normal validations when the user exceeds the condition i want to store the message into out parameter and i want to return that message into the application. is it possible or not
waiting for valuable reply
Sri

If you want to abort processing after the error, I would add a RETURN statement immediately after the RAISERROR (still in the BEGIN...END block.)

Code Snippet

CREATE PROCEDURE Emp_Ins
( @.no integer,
@.name varchar(20),
@.sa integer,
@.msg varchar(20) OUTPUT
AS
IF ( @.no < 0 )
BEGIN
SET @.msg = 'Empno > 0'
RAISEERROR( @.msg, 10, 1 )
RETURN
END
INSERT INTO Emp VALUES ( @.no, @.name, @.sa )

|||Thank u Arnie.
Before u r reply i tried with the same code i got it additionally u given a point that with return statement transaction will be aborted.Arnie u may feel that it may be silly in case of multiple ifs how to write the program like prevoius program because i m ne to sqlserver envi i m much familiar with envi thats wy i m requesting u
Thanks and Regards
Sridhar
|||

Baba urf Sivaji wrote:

i want to return that message into the application. is it possible or not

You should use the proper Severity number & it should be 16 rather 10. You can return the control after the Raiseerror. Now the message will be captured on your UI & the control alos return back to the UI.

RAISERROR (@.msg,16,1);

Return;

|||You need to have the application declare an OUTPUT parameter to capture the value of @.Msg.