CODEWITHSUNDEEP
sql-serverstored-procedures
Ketul Soni
Ketul Soni

Reputation: 85

Error Handle in sql server

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.

Upvotes: 2

Views: 63

Answers (3)

Coder
Coder

Reputation: 408

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

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

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;

Upvotes: 0

Anton
Anton

Reputation: 2882

You can use TRY ... CATCH

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

Upvotes: 0

Related Questions