When you work in a offshore - onsite setup in IT industry and if you are fortunate 🙂 to be a part of the offshore team then there are many compromises that you have to make. Out of those one such is to develop your code based on some illogical limitations enforced upon you by the onsite development/dba team. That is where you improvise and learn.

I am going to throw some light on one such situation I faced in my last project. But this time thankfully the onsite dba gave me some tips to go about my task.

Requirement :- You are given a database that has some tables in it. However we cannot access these tables directly so we are given access to some user defined functions only. For example one such table is emp_man which looks like this:

and there is udf corresponding to this i.e.
/////////////////////////////////////////////////////////////////////////////////////////////
create function ufn_getempl (@hrdate datetime) returns table
  as

  return
  select * from emp_man where hiredate =@hrdate
/////////////////////////////////////////////////////////////////////////////////////////////

Now the task is to develop a procedure to return us list of employees who have hiredate falling between startdate and enddate using above function.

SOLUTION :

So the challenge is that function expects only hiredate whereas we need to design the stored procedure that expects startdate and enddate. I came up with the logic that we will add a day to the startdate and execute function and we will do it till the end date is reached. So in terms of implementation we will be using cross apply operator and a config table (can be a temporary and permanent result set which will be nothing but hold increment values from 1 to number of days for which the system is expected to work )

there is how sp looks like

/////////////////////////////////////////////////////////////////////////////////////////////
alter proc usp_getemp (@startdate datetime, @enddate datetime)
  as
  select e.empid, e.hiredate from
  config c cross apply
  ufn_getempl(dateadd(dd,c.sno-1,@startdate)) e
  where
  (datediff(dd,@startdate , @enddate) >=sno or sno=1)
/////////////////////////////////////////////////////////////////////////////////////////////

and table looks like this:-

so if we execute the stored proc using //exec usp_getemp '01-01-2001','01-01-2001'// on the same table we ll get following result

IMPORTANT NOTE : for this scenario we need to what would be the maximum daterange that should be passed to the stored procedure, because based on that we ll be inserting values inside the config table