How to create and call SQL Server Function


To create a SQL server function, first code a sql file such as the following one;

USE [test] –name of the Database
GO
/****** Object:  UserDefinedFunction [dbo].[ROLEDESCRIPTIONFINDER]    Script Date: 05/22/2011 01:23:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ROLEDESCRIPTIONFINDER]
(
 @ROLE_NUMBER INT 
 — USED AS AN INPUT FOR THE FUNCTION
)
RETURNS VARCHAR(50) –RETURN VALUE
AS
BEGIN
 
 — DECLARE THE RETURN VARIABLE
 — INCLUDING THE DESCRIPTION OF ROLES
 
 DECLARE @ROLE_DESCRIPTION VARCHAR(50)

 SELECT @ROLE_DESCRIPTION = –SETTING DESCRIPTION
  CASE @ROLE_NUMBER
   WHEN 1 THEN ‘IT_PROGRAMMER’
   WHEN 2 THEN ‘MANAGER’
   WHEN 3 THEN ‘DBADMIN’
   WHEN 4 THEN ‘WEBSERVICE_DEVELOPER’
   WHEN 5 THEN ‘SYSTEM_ADMIN’
   WHEN 6 THEN ‘SOFTWARE_DEVELOPER’
   WHEN 7 THEN ‘CONTACT_CENTER_AGENT’
   WHEN 8 THEN ‘TEAM_LEADER’
   ELSE ‘DEFAULT_ROLE’
  END

 RETURN @ROLE_DESCRIPTION —RETURN VALUE
END

And call it;

SELECT [test].[dbo].[ROLEDESCRIPTIONFINDER] (4) AS “DESCRIPTION_RETURNED” 

Result:

DESCRIPTION_RETURNED
————————————————–
WEBSERVICE_DEVELOPER

(1 row(s) affected)

Advertisements

SQL Server Rollback, Transaction Management and Exception Handling Example


USE test; –database name
GO

IF (OBJECT_ID(‘[test].[dbo].[Employees]’) IS NULL) –if the table was not created before create it
CREATE TABLE [test].[dbo].[Employees](
[employee_id] [int] NOT NULL,
[employeee_name] [varchar](50) NULL,
[employee_surname] [varchar](50) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
([employee_id] ASC
)

)
BEGIN TRAN INSERT_EMPLOYEE
BEGIN

INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(1,’Test Employee Name 1′, ‘Test Employee Surname 1′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(2,’Test Employee Name 2’, ‘Test Employee Surname 2′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(3,’Test Employee Name 3’, ‘Test Employee Surname 3′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(4,’Test Employee Name 4’, ‘Test Employee Surname 4’);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(4,’Test Employee Name 4′, ‘Test Employee Surname 4’); –trying to add the same employee again causing an exception

IF @@ERROR != 0 –check @@ERROR variable and Rollback the whole trancation if an error occurs
BEGIN
ROLLBACK TRAN INSERT_EMPLOYEE –rollback the whole transaction (all INSERT statements)
END
ELSE
BEGIN
COMMIT TRAN INSERT_EMPLOYEE; –Commit the transaction if the operation is successful
END
END

Calling a SQL Server Stored Procedure using JAVA


First create a procedure;

CREATE PROCEDURE GetHighestSalary
@departmentID INT,
@salary VARCHAR
OUTPUT AS
BEGIN
SELECT @salary = MAX(salary)
FROM SALARY
WHERE departmentID = @departmentID
END

Next call it with the following code;

public static String executeStoredProcedure(Connection conn) {
String maxSalary =””;
try { 
         CallableStatement statement= conn.prepareCall(“{call dbo.GetHighestSalary(?, ?)}”); 
         statement.setInt(1, 101); //—–>departmentID
         statement.registerOutParameter(2, java.sql.Types.VARCHAR); //—–>maximum SALARY  
         statement.execute(); maxSalary = statement.getString(2);
} catch (Exception ex) {
ex.printStackTrace();
}
return maxSalary;
}