Select Stored Procedure In SQL Server To Get Data With Try Catch

Hello frnds this is my another article on stored procedure in which I will explain  how to get data from database using stored procedure .Also I will use exception handling in this stored procedure for handling runtime error so let’s start  

Step 1: create a database in sql server name it what you want .Now create a table for demo purpose I have create a demotable whose script are shown in the below code

CREATE TABLE COMMAND

CREATE TABLE [dbo].[Demo_User](

      [User_ID] [int] IDENTITY(1,1) NOT NULL,

      [User_Name] [varchar](100) NOT NULL,

      [User_Mail] [varchar](100) NOT NULL,

 CONSTRAINT [PK_demo_user] PRIMARY KEY CLUSTERED

(

      [User_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now our table is ready to go .

Step 2: Now create stored procedure to get data from it before proceed it please put some data in the table

SELECT STORED PROCEDURE

Alter PROCEDURE sp_Get_DemoUser

     

AS

BEGIN Try

     

select [User_Name],[User_Mail] from Demo_User order by [User_Id] desc

End Try

begin catch

SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

                   

·         ERROR_NUMBER() returns the number of the error.

·         ERROR_SEVERITY() returns the severity.

·         ERROR_STATE() returns the error state number.

·         ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

·         ERROR_LINE() returns the line number inside the routine that caused the error.

·         ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

 I hope this will helpful for you .

Thanks