April 10, 2009 at 12:45 pm
hi,
In my case i m using a stored procedure to inert a value into the table.
CREATE PROCEDURE Test
@Father VarChar(6),
@Mother VarChar( 8),
@Son VarChar( 4),
@Daughter VarChar( 10),
begin
set nocount ON
insert into family (Father,Mother,son,Daughter)VALUES (@Father , @Women , @Son, @Daughter )
if @@error != 0
begin
select @@error 'rc'
return @@error
end
else
begin
select 0 'rc'
return 0
end
end
All the four fields are NOT NULL.I m trying executing the stored procedure
like this
exec test 'KING','Queen','Prince',null
Since I m passing null i getting a error like
column does not allow nulls. INSERT fails
But the stored procdure is returning 0 Ie returning success.Tried to fix this but couldnt.
Can some one help me out to fix this
Thanks in advance
April 13, 2009 at 6:34 am
From BOL for @@error:
Returns 0 if the previous Transact-SQL statement encountered no errors.
So as soon as you have a statement after the error then the @@error returns 0.
If you are really using 2005 you should be using Try - Catch error handling. See this article on how to do this.
If you are using 2000 then you should do something like this:
-- create error
SELECT 1/0
-- show error number
SELECT @@error
-- create error
SELECT
1/0
-- check if error occurred
IF @@ERROR > 0
BEGIN
-- show error number which has reset because of IF
SELECT @@error
END
-- here's how you need to do it with @@error
DECLARE @error INT
-- create error
SELECT 1/0
SELECT @error = @@ERROR
IF @error <> 0
BEGIN
SELECT @error as err_number, 'Error Occured' AS msg, @@ERROR AS ataterror
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply