ashish

Functions and Stored Procedures have few similarities such as:

• They are group of T-SQL statements which is stored on the server.These group of T-SQL statements are executed as a unit.
• Abstracts the developer from the implementation details since he needs to just call the procedure or function.
• Accepts parameters and return values.
• Are used to create reusable logic.This makes the code easy to maintain.

Though Stored procedure and function may seem to be similar,their purpose is quite different.

• Stored procedures may contain statements which modifies the state of the database.Stored Procedures may contain statements such as insert or delete.On the contrary functions are used to just return the data to the caller.Functions can not contain statements such as insert which modifies the existing state of the database.So Insert/Update/Delete statements are not valid in a SQL Server function.

• Stored Procedures can return multiple result sets.On the other hand Functions can only return a single result set.So for returning multiple result sets stored procedure has to be used.

• Stored procedure can call a function.Function can not call a stored procedure.Though a function can call another function or even itself.

• Function can be used in a select statement while Stored Procedure can not be used in a select statement.
  Following function returns all the users in the UserInfo table

CREATE FUNCTION dbo.fnGetAllUsers()
RETURNS TABLE
AS
RETURN
SELECT * FROM UserInfo

fnGetAllUsers() function can be used in a select statement as:

SELECT * FROM fnGetAllUsers()

The ability to use functions in select statement allows developer to break a complex logic into functions.

• Stored procedure can define exception handling logic while Function can not contain exception handling logic such as TRY.. CATCH or RAISERROR.

• Stored Procedures can define transactions whereas Functions do not allow transactions.

• It is mandatory for a function to return a value whereas Stored Procedures might or might not return a value. Though Functions can return different types of data such as a Table or a Scalar value but Functions always return some data to the caller.

     CREATE FUNCTION FunctionName
      (
         parameter list
      )
       RETURNS DataType
         AS

     Function Body

So the RETURN keyword is part of the function definition.

• Stored Procedures are precompiled whereas functions are not precompiled.

ashish

Exception is a runtime error which prevents the normal execution of code.There are different  ways of error handling in SQL Server.

Each error in SQL Server has certain information attached to it.Some of the important information attached to each error is:

• Message Number     Each error has a error number.
• Severity  Number     Each error has a associated severity number.This number identifies the severity of the error.For example severity numbers from 1 to 10 do not specify an error.So messages in this range could be informational or warning.Severity numbers from 11-16 specifies programming errors.
• Error Text   The text of the error.

Errors which SQL Server can generate are stored in the sys.messages catalog view.So if we want to see the error details we can use the following query:

select * from sys.messages

This will display the list of errors defined by sql server as:

 Returning Error Information using RAISERROR

We can raise the errors ourselves depending on the application logic.Raiserror is a system function which is used to raise and send the errors to the calling procedure or the application.The application which calls the procedure should be informed about the success or failure of the procedure.
If the calling application is informed about the error in the execution of the procedure or the query then it can take appropriate action to fix the error.

We can pass the message and other error details to the RAISERROR function which is returned to the calling application.

Following is the syntax to call the RAISERROR() function.

RAISERROR ('Error Message', Severity, State);

The following will return a message with severity 10.

RAISERROR('This is just a message',10,1);

If we execute the above statement in Query Editor we get the following output.

Some useful severity numbers are:

• 10      No Error occurred.Used for returning just a message
• 15      Warning
• 16      Error Occurred.Statement could not execute

State is a integer from 0 to 255.It is used to specify different values so the source of the error can be identified.

Error handling using @@error

@@error is a global variable which contains the error number of the last executed statement.It is updated after the execution of each statement.

If the last command executed successfully then @@error will contain the value 0.If we execute the following commands

PRINT 'HELLO FROM SQL SERVER'
PRINT @@error

then 0 is printed as the value of @@error since the first PRINT statement successfully executes.

It is important to understand that the value of @error becomes 0 if the most recent statement executed successfully.So it is useful to store the error number in another variable if we want to retrieve the error number later on.

Error handling using TRY CATCH

Using @@error was the only way of implementing error handling in the previous versions of SQL Server.SQL Server 2005 introduced the TRY CATCH statements for error handling.In SQL Server 2005 and later versions TRY CATCH is a better option of handling errors.

TRY CATCH is implemented in SQL Server as:

BEGIN TRY
T-SQL STATEMENT BLOCK
END TRY
BEGIN CATCH
T-SQL STATEMENTS BLOCK TO HANDLE THE ERROR
END CATCH;
T-SQL STATEMENTS

So the statements marked on the line number 2,5 and 7 above will execute when

• Statement on the line number 2 is the statement which we want to monitor for the error.If it causes any error then the execution jumps to the catch block.
• Statement in the catch block e.g line number 5 is executed if there is any error in the try block e.g line number 2.
• Statements after the catch block e.g line number 7 will be executed after the catch block finishes execution.

If the try block calls a stored procedure and the procedure throws an error then the control is transferred to the catch block of the calling statement.

Example of TRY .. CATCH

The UserInfo table contains the user information.

CREATE TABLE [dbo].[UserInfo](
[UserName] [varchar](50) NULL,
[FIRSTNAME] [varchar](50) NULL,
[LASTNAME] [varchar](50) NULL,
[COUNTRY] [varchar](50) NULL,
[ID] [int] NULL
) ON [PRIMARY]

It defines a Id column which is of type int.In the try block we are inserting a row in the  UserInfo table.But instead of int value we are passing a string value.This will cause an exception in the try block.We handle this exception in the try block

BEGIN TRY
PRINT 'INSIDE TRY BLOCK'
DECLARE @ID INT
SELECT @ID=ID FROM [dbo].[UserInfo] WHERE ID=6
SELECT CONVERT(INT,@ID)
DECLARE @INCREMENTID INT
SELECT @INCREMENTID= @ID+1
PRINT @INCREMENTID

INSERT INTO [dbo].[UserInfo]
([UserName]
,[FIRSTNAME]
,[LASTNAME]
,[COUNTRY]
,[ID])
VALUES
('harry'
,'HARRY'
,'POTTER'
,'UK'
,'A001')

PRINT 'THIS IS PRINTED IF THERE IS NO ERROR IN THE TRY BLOCK'
END TRY
BEGIN CATCH

PRINT ERROR_MESSAGE()

PRINT 'INSIDE CATCH BLOCK'
END CATCH;
PRINT 'THIS EXECUTES AFTER THE TRY BLOCK.THIS WILL EXECUTE WHETHER OR NOT ERROR OCCURS IN THE TRY BLOCK'

Useful system functions 

There some useful system functions which can be used in the TRY block to obtain detailed information about the error.Some of these functions are:

• ERROR_NUMBER()                 returns the error number.
• ERROR_SEVERITY()               returns the error severity.
• ERROR_STATE()                      returns the state number of the error.
• ERROR_PROCEDURE()         returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE()                         returns the line number in the stored procedure or trigger which caused the error.
• ERROR_MESSAGE()               returns the Error Message string.

ashish