t-sql wrapper for store procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

t-sql wrapper for store procedure

Author

Topic

zahid.sabreen
Starting Member

14 Posts

Posted - 2011-01-19 : 00:46:22

USE [EAS_APJK]
GO
/****** Object: StoredProcedure [dbo].[sp_getvacationforapproval] Script Date: 01/19/2011 11:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_getvacationforapproval] @employeeID varchar(10), -- ID of approver
@actionRequired varchar(1) -- 1 - show action required only
-- 0 - show all

/*
For installing in database:
DROP PROCEDURE sp_getvacationforapproval
GRANT execute on sp_getvacationforapproval to EAS_MANAGER, EAS_ADMIN

For testing purposes:
exec sp_getvacationforapproval '0000000096','0'

Comments:

Country specific changes:
+-------------+-------------------------+---------------------------------
| Date | Modifier Name | Short Modification Description
+-------------+-------------------------+---------------------------------
|16.5.2003 |Daniel Weimer |added new column for account type
| | |description
| | |
| | |changed query: now, all requests
| | |of employees that *currently* report
| | |to the manager are shown (before
| | |all requests for the latest approver
| | |were shown)
+-------------+-------------------------+---------------------------------
| 10.11.03 |Seb | added conversion to hours for German Travel Account
| 2003-11-26 |ja |bug: changed where clause vacation select
| | |
+-------------+-------------------------+---------------------------------
| 23.02.2004 |Seb | Added account id to identify account
+-------------+-------------------------+---------------------------------
| 05.03.2004 |Seb | Replaced hardcoded account name for travel account
| | | with account id
+-------------+-------------------------+---------------------------------
| 08.04.2004 |Seb | For italian ROL we need to execute sp_getnumberofhours
+-------------+-------------------------+---------------------------------
| 30.03.2005 |Fabian Wleklinski | Implemented special treatment
| | | for hourly accounts.
+-------------+-------------------------+---------------------------------
*/

AS
BEGIN

SET NOCOUNT ON

-- PLEASE turn debug messages off if used LIVE system !!!!
declare @i_DebugMode int
select @i_DebugMode = 0 -- means no debug messages send out from the stored procedure
-- select @i_DebugMode = 1 -- you got all the messages

IF @i_DebugMode = 1 select 'Start of sp_getvacationforapproval'

--begin-of-implementation-----------------------------------------------------------------

-- required information in T_EMPLOYEE_VACATION
declare @_EMPLOYEE_ID varchar(10),
@_VACATION_STARTDATE datetime,
@_VACATION_ENDDATE datetime,
@_NUMBER_CURRENT_DAYS decimal(5,2),
@_EMPLOYEE_VACATION_ID varchar(10),
@_ACCOUNT_NAME varchar(50),
@_ACCOUNT_ID varchar(10),
@n_DAILY_HOURS decimal(5,2),
@v_GERMANTRAVELACCOUNTID varchar(10),
@v_ITALIANROLACCOUNTID varchar(10),
@v_HOURLYACCOUNTID varchar(10),
@d_TODAY datetime

Select @d_TODAY = getdate(),
@v_GERMANTRAVELACCOUNTID = '0000000003',
@v_ITALIANROLACCOUNTID = '0000000004',
@v_HOURLYACCOUNTID = '0000000005'

-------------------------------------------------------------------------------------------
-- Get the calculation factor (T_CONTRACT._DAILY_HOURS) for travel account
-------------------------------------------------------------------------------------------

-- get daily hours FROM current contract
SELECT @n_DAILY_HOURS = T_CONTRACT_TYPE._DAILY_HOURS
FROM T_CONTRACT, T_CONTRACT_TYPE
WHERE T_CONTRACT._EMPLOYEE_ID = @employeeID
AND T_CONTRACT._CONTRACT_TYPE_ID = T_CONTRACT_TYPE._CONTRACT_TYPE_ID
AND T_CONTRACT._STARTDATE <= @d_TODAY
AND (T_CONTRACT._ENDDATE >= @d_TODAY OR T_CONTRACT._ENDDATE is null)

-- if no current contract is found, try to get older contract
IF(@n_DAILY_HOURS is Null)
BEGIN
SELECT @n_DAILY_HOURS = T_CONTRACT_TYPE._DAILY_HOURS
FROM T_CONTRACT, T_CONTRACT_TYPE
WHERE T_CONTRACT._EMPLOYEE_ID = @employeeID
AND T_CONTRACT._CONTRACT_TYPE_ID = T_CONTRACT_TYPE._CONTRACT_TYPE_ID
AND T_CONTRACT._ENDDATE
IN (SELECT MAX(T_CONTRACT._ENDDATE)
FROM T_CONTRACT, T_CONTRACT_TYPE
WHERE T_CONTRACT._EMPLOYEE_ID = @employeeID
AND T_CONTRACT._CONTRACT_TYPE_ID = T_CONTRACT_TYPE._CONTRACT_TYPE_ID
AND T_CONTRACT._ENDDATE < @d_TODAY
)
END

-- if no current contract and no older contract is found, try to get future contract
IF(@n_DAILY_HOURS is Null)
BEGIN
SELECT @n_DAILY_HOURS = T_CONTRACT_TYPE._DAILY_HOURS
FROM T_CONTRACT, T_CONTRACT_TYPE
WHERE T_CONTRACT._EMPLOYEE_ID = @employeeID
AND T_CONTRACT._CONTRACT_TYPE_ID = T_CONTRACT_TYPE._CONTRACT_TYPE_ID
AND T_CONTRACT._STARTDATE
IN (SELECT MIN(T_CONTRACT._STARTDATE)
FROM T_CONTRACT, T_CONTRACT_TYPE
WHERE T_CONTRACT._CONTRACT_TYPE_ID = T_CONTRACT_TYPE._CONTRACT_TYPE_ID
AND T_CONTRACT._STARTDATE > @d_TODAY
)
END
-- if still no contract is found, then there is no contract!!!
if(@n_DAILY_HOURS = Null)
begin
select @n_DAILY_HOURS = 0
if @i_DebugMode = 1 print 'No contract found for this employee.'
end

------------------------------------------------------------------------------
-- Cursor
------------------------------------------------------------------------------

-- TMP Table for cursor
create table #daniel
(
_EMPLOYEE_ID varchar(10),
_VACATION_STARTDATE datetime,
_VACATION_ENDDATE datetime,
_NUMBER_CURRENT_DAYS decimal(5,2),
_EMPLOYEE_VACATION_ID varchar(10),
_ACCOUNT_NAME varchar(50),
_ACCOUNT_ID varchar(10)
)

-- cursor over all vacations to approve
declare cur_vacation cursor
for

-- ja-2003-11-26: correction of where clause
select T_EMPLOYEE_VACATION._EMPLOYEE_ID,
T_EMPLOYEE_VACATION._VACATION_STARTDATE,
T_EMPLOYEE_VACATION._VACATION_ENDDATE,
T_EMPLOYEE_VACATION._EMPLOYEE_VACATION_ID,
T_ACCOUNT_TYPE._NAME,
T_ACCOUNT_TYPE._ACCOUNT_TYPE_ID
from T_EMPLOYEE_VACATION, T_VACATION_TYPE, T_ACCOUNT_TYPE
where T_EMPLOYEE_VACATION._LOCATION_ID = T_VACATION_TYPE._LOCATION_ID
and T_EMPLOYEE_VACATION._VACATION_TYPE_ID = T_VACATION_TYPE._VACATION_TYPE_ID
and T_ACCOUNT_TYPE._ACCOUNT_TYPE_ID = T_VACATION_TYPE._ACCOUNT_TYPE_ID
and T_EMPLOYEE_VACATION._SUBMIT_FLAG = '1'
and ('0' = @actionRequired
or ((T_EMPLOYEE_VACATION._REJECT_FLAG != '1'
or T_EMPLOYEE_VACATION._REJECT_FLAG is null)
and (T_EMPLOYEE_VACATION._APPROVE_CLOSE_FLAG != '1'
or T_EMPLOYEE_VACATION._APPROVE_CLOSE_FLAG is null)))
and T_EMPLOYEE_VACATION._EMPLOYEE_ID in
(select T_EMPLOYEESTATUS._EMPLOYEE_ID
from T_EMPLOYEESTATUS
where (getdate() between T_EMPLOYEESTATUS._START_DATE and T_EMPLOYEESTATUS._END_DATE
or T_EMPLOYEESTATUS._END_DATE = '01-01-2500')
and (T_EMPLOYEESTATUS._REPORTTOEMP_ID = @employeeID
or T_EMPLOYEESTATUS._REPORTTOEMP_ID in
(select T_EMPLOYEE_SUBSTITUTION._EMPLOYEE_ID
from T_EMPLOYEE_SUBSTITUTION
where T_EMPLOYEE_SUBSTITUTION._SUBSTITUTE_ID = @employeeID
and (T_EMPLOYEE_SUBSTITUTION._SUBSTITUTE_FLAG = '1'
or T_EMPLOYEE_SUBSTITUTION._ON_BEHALF_FLAG = '1')
and T_EMPLOYEE_SUBSTITUTION._VALID_FLAG = '1'
and T_EMPLOYEE_SUBSTITUTION._STARTDATE <= CONVERT(DATETIME,CONVERT(VARCHAR,getdate(), 102), 102)
and (_ENDDATE >= getdate()
or _ENDDATE is null))))

open cur_vacation

fetch cur_vacation into
@_EMPLOYEE_ID,
@_VACATION_STARTDATE,
@_VACATION_ENDDATE,
@_EMPLOYEE_VACATION_ID,
@_ACCOUNT_NAME,
@_ACCOUNT_ID

WHILE @@FETCH_STATUS = 0
BEGIN

-- Calculate NUMBER_CURRENT_DAYS
if(@_ACCOUNT_ID != @v_ITALIANROLACCOUNTID and @_ACCOUNT_ID != @v_HOURLYACCOUNTID)
exec sp_getnumberofdays @_EMPLOYEE_ID, @_VACATION_STARTDATE, @_VACATION_ENDDATE,'1','1', @_NUMBER_CURRENT_DAYS output
else
exec sp_getnumberofhours @_EMPLOYEE_ID, @_VACATION_STARTDATE, @_VACATION_ENDDATE,
'1','1', 'H', @_NUMBER_CURRENT_DAYS output

if(@_ACCOUNT_ID = @v_GERMANTRAVELACCOUNTID)
SELECT @_NUMBER_CURRENT_DAYS = @_NUMBER_CURRENT_DAYS * @n_DAILY_HOURS

insert into #daniel select
@_EMPLOYEE_ID,
@_VACATION_STARTDATE,
@_VACATION_ENDDATE,
@_NUMBER_CURRENT_DAYS,
@_EMPLOYEE_VACATION_ID,
@_ACCOUNT_NAME,
@_ACCOUNT_ID

fetch cur_vacation into
@_EMPLOYEE_ID,
@_VACATION_STARTDATE,
@_VACATION_ENDDATE,
@_EMPLOYEE_VACATION_ID,
@_ACCOUNT_NAME,
@_ACCOUNT_ID

END
close cur_vacation
deallocate cur_vacation

-- SELECT ALL NEEDED INFORMATION
SELECT
#daniel._EMPLOYEE_ID,
#daniel._EMPLOYEE_VACATION_ID,
T_EMPLOYEE._LASTNAME + ', ' + T_EMPLOYEE._FIRSTNAME + ' (' + T_EMPLOYEE._EMPLOYEE_INTERNATIONAL_NO + ')' AS _FULLNAME,
#daniel._VACATION_STARTDATE as _STARTDATE,
#daniel._VACATION_ENDDATE as _ENDDATE,
CASE WHEN (#daniel._NUMBER_CURRENT_DAYS = 1.0 AND T_EMPLOYEE_VACATION._AM_PM_FLAG in ('0','1'))
THEN 0.5
ELSE #daniel._NUMBER_CURRENT_DAYS
END as _NUMBER_CURRENT_DAYS,
T_VACATION_TYPE._NAME,
T_EMPLOYEE_VACATION._COMMENT,
T_EMPLOYEE_VACATION._APPROVE_CLOSE_FLAG,
T_EMPLOYEE_VACATION._REJECT_FLAG,
T_EMPLOYEE_VACATION._AM_PM_FLAG,
'0' as _DECISION_ID,
#daniel._ACCOUNT_NAME,
#daniel._ACCOUNT_ID
FROM #daniel, T_EMPLOYEE, T_EMPLOYEE_VACATION, T_VACATION_TYPE
WHERE
#daniel._EMPLOYEE_ID = T_EMPLOYEE._EMPLOYEE_ID
AND #daniel._EMPLOYEE_ID = T_EMPLOYEE_VACATION._EMPLOYEE_ID
AND #daniel._EMPLOYEE_VACATION_ID = T_EMPLOYEE_VACATION._EMPLOYEE_VACATION_ID
AND T_EMPLOYEE_VACATION._VACATION_TYPE_ID = T_VACATION_TYPE._VACATION_TYPE_ID
AND T_EMPLOYEE_VACATION._LOCATION_ID = T_VACATION_TYPE._LOCATION_ID
order by _FULLNAME ASC, #daniel._VACATION_STARTDATE ASC

-- drop temporary table
drop table #daniel

--end-of-implementation-------------------------------------------------------------------

IF @i_DebugMode = 1 select 'End of sp_getvacationforapproval'

RETURN 0 -- sp execution successful
END --of as begin

This is the store procedure for which a wrapper has to be written in t-sql.
please help how to proceed.

Thanks,
SABREEN

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-19 : 07:17:04

A wrapper for a stored procedure would not be written in T-SQL, it would be written in the application's or framework's main language (C/C++/C#, VB.Net, Java, etc.) That wrapper would call the stored procedure directly.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-19 : 14:03:38

I'm with Rob, that you'd wrap this in a different language. But for grins:

CREATE PROCEDURE [dbo].[Wrapper_for_sp_getvacationforapproval] 
(
    @employeeID varchar(10), -- ID of approver
    @actionRequired varchar(1) -- 1 - show action required only 0 - show all
) AS
BEGIN

EXEC [dbo].[sp_getvacationforapproval] @employeeID , @actionRequired

END