Here is the code for the stored procedure:
USE [AdventureWorks2012]
GO
CREATE PROCEDURE getEmployeeDetail
@businessEntityIDIn INT,
@businessEntityIDout INT OUTPUT,
@loginId nvarchar(256) OUTPUT,
@organizationNode nvarchar(4000) OUTPUT,
@organizationLevel smallint OUTPUT,
@birthDate date OUTPUT,
@maritalStatus nchar(1) OUTPUT,
@salariedFlag Flag OUTPUT,
@vacationHours smallint OUTPUT,
@rowguid uniqueidentifier OUTPUT,
@modifiedDate datetime OUTPUT
AS
BEGIN
SELECT
@businessEntityIDout = [BusinessEntityID],
@loginId = [LoginID],
@organizationNode = [OrganizationNode].ToString(),
@organizationLevel = [OrganizationLevel],
@birthDate = [BirthDate],
@maritalStatus = [MaritalStatus],
@salariedFlag = [SalariedFlag],
@vacationHours = [VacationHours],
@rowguid = [rowguid],
@modifiedDate = [ModifiedDate]
FROM HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityIDIn
RETURN 42
END
Here is how to call it in SQL Server:
USE [AdventureWorks2012]
GO
DECLARE @return_value int,
@mybusinessEntityIDout INT,
@myloginId nvarchar(256),
@myorganizationNode nvarchar(4000),
@myorganizationLevel smallint,
@mybirthDate date,
@mymaritalStatus nchar(1),
@mysalariedFlag Flag,
@myvacationHours smallint,
@myrowguid uniqueidentifier,
@mymodifiedDate datetime
EXEC @return_value = [dbo].[getEmployeeDetail]
@businessEntityIDIn = 100,
@businessEntityIDout = @mybusinessEntityIDout OUTPUT,
@loginId = @myloginId OUTPUT,
@organizationNode = @myorganizationNode OUTPUT,
@organizationLevel = @myorganizationLevel OUTPUT,
@birthDate = @mybirthDate OUTPUT,
@MaritalStatus = @mymaritalStatus OUTPUT,
@salariedFlag = @mysalariedFlag OUTPUT,
@vacationHours = @myvacationHours OUTPUT,
@rowguid = @myrowguid OUTPUT,
@modifiedDate = @mymodifiedDate OUTPUT
SELECT
@mybusinessEntityIDout as N'@mybusinessEntityIDout',
@myloginId as N'@myloginId',
@myorganizationNode as N'@myorganizationNode',
@myorganizationLevel as N'@myorganizationLevel',
@mybirthDate as N'@mybirthDate',
@mymaritalStatus as N'@mymaritalStatus',
@mysalariedFlag as N'@mysalariedFlag',
@myvacationHours as N'@myvacationHours',
@myrowguid as N'@myrowguid',
@mymodifiedDate as N'@mymodifiedDate'
SELECT 'Return Value' = @return_value
GO
Note: There are three data types defined in the HumanResources.Employee table that need special attention.
-
OrganizationNode is defined with a "hierarchyid" data type.
See MSDN Library for more on the hiearchyid. To handle this type the .ToString() was called in the stored procedure. The java code is then able to consume it as a String
-
SalariedFlag is defined as a Flag(bit).
In this case Flag is an Alias Data Type for bit. bit types can be consumed in Java as a boolean.
-
rowguid is defined as a uniqueidentifier.
Which can be consumed as a String in Java
The point is that you may need to find the right mapping from the data type used in the database vs the type used in Java. This is a good resource to Understand the JDBC Driver Data Types. It also some a handy tables that show how they are mapped. If needed, the conversion to a valid type can be done in the stored procedure.