Error Handle in sql server

Question

DECLARE @id bigint=0,
    @id int=0,
    @name varchar(50) = '36',
    @marks int = 'SDFGS'
    @Op varchar(50) = 'UPSERT'

    IF(@Op='UPSERT')
    BEGIN
        INSERT INTO tbl_student     
        (name, marks)
        VALUES 
        (@name, @marks)
        SELECT SCOPE_IDENTITY()     
    END
    ELSE
    BEGIN
         UPDATE tbl_student SET
         name = @name,
         marks = @marks
         WHERE id = @id
         SELECT 'Success'
    END

It throw error 'Conversion failed when converting the varchar value 'SDFGS' to data type int.'

I want to handle this error.

If error then it will be return 'Error' string.

Coder · Accepted Answer · 2016-08-31 06:10:33Z

1

You can handle this error using TRY... CATCH Block

Begin
declare @msg varchar(100)
Begin try
DECLARE @id bigint=0,@name varchar(50) = '36',@marks int = 'SDFGS',@Op varchar(50) = 'UPSERT'

IF(@Op='UPSERT')
BEGIN
    INSERT INTO tbl_student     
    (name, marks)
    VALUES 
    (@name, @marks)
    SELECT SCOPE_IDENTITY()     
END
ELSE
BEGIN
     UPDATE tbl_student SET
     name = @name,
     marks = @marks
     WHERE id = @id
     SELECT 'Success'
     Set @msg='Success'
END

End try
Begin catch
SELECT 'Error'
Set @msg='Error'
 End catch

End

answered Aug 31, 2016 at 6:10

Coder

4089 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton · Accepted Answer · 2016-08-31 05:26:41Z

0

You can use TRY ... CATCH

https://msdn.microsoft.com/en-us/library/ms175976.aspx - there is a sample code here.

answered Aug 31, 2016 at 5:26

Anton

2,8821 gold badge12 silver badges15 bronze badges

Comments

Rahul Tripathi · Accepted Answer · 2016-08-31 05:27:33Z

0

The error says it all, you are trying to put a string value in an int datatype and hence the error. If you want to catch this error then try to use TRY...CATCH. Something like

BEGIN TRY  
    -- Your code.   
END TRY  
BEGIN CATCH  
    -- Catch the exception/error here.   
END CATCH;

answered Aug 31, 2016 at 5:27

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

Error Handle in sql server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related