asked on

MS SQL Function Parameters - can these be outputs of another function without using temporary variables?

Select [VariableList] From [DB].[dbo].[func_A](J.[iVar], J.[dVar1], J.[dVar2])
OUTER APPLY (
Select J1.[iVar], J1.[dVar1], J1.[dVar2] From [DB].[dbo].[func_B](@VarP, @VarQ) J1
) AS J

I have a function that lists date ranges, and I want to use these ranges to restrict the output sets of another function.

The easy way is to loop through the output of func_B using a cursor (or place the output in a temporary table, and use a WHILE LOOP).

I was wondering if there is a way to join directly?

PortletPaul

does FUNC_B return a table of values? if it did you can treat it "as if it were" a table

can you post the code of FUNC_B?

Bird757

ASKER

Both functions are table functions

Func_B returns
Int, Date1, Date2
Int, Date1b, Date2b

Func_A Uses Int, Date1, Date2 to return an array

So what I can do is:
Declare TABLE @TableB (iCntr [int] IDENTITY(1, 1), iInt [int], dDate1 [DateTime], dDate2 [DateTime])
Declare TABLE @TableA (cVarList [VarChar](2000))

Declare @RowCntr Int
INSERT INTO @TableB ([iInt], [dDate1], [dDate2])
Select * From [func_B] (@IntVar, @DateVar1, @DateVar2)

Set @RowCntr = @@ROWCOUNT

Declare @iCntr Int
Set @iCntr = 0
WHILE @iCntr < @RowCntr
BEGIN
Set @iCntr = @iCntr + 1
Select @IntVar = [iVar], @DateVar1 = [dDate1], @DateVar2 = [dDate2] From @TableB Where [iCntr] = @iCntr

INSERT INTO @TableA
Select [VarList] From [func_A](@IntVar, @DateVar1, @DateVar2)
END

Select * From @TableA /* this is the result set I am looking for */

It just seems really long-winded when I am sure there is a way to push the outputs of fund_B directly into the inputs of func_A?

By the way - SQL2012 is what I am using.

Thanks

PortletPaul

can you post the actual code for each function?

is this literally all func_b does:? (2 rows)

Func_B returns
Int, Date1, Date2
Int, Date1b, Date2b

why do you need a function for this?

Right now I'm mighty confused, actual code would help.

Bird757

ASKER

The actual scenario;
Function (B) returns the times a vehicle starts and stops.
Function (A) receives as input a Vehicle ID, and 2 times, and returns the full GPS Logs collected between the 2 times.

I take the output of (A), and generate a Google Earth file that shows where a vehicle has travelled.

The reason for joining to (B) - the Vehicle Start/Stop - is so I can change the line colour for each of the Travel Intervals.

Function B can return any number of rows, depending on whether the user wants to see 1 day, or 1 year.

Function A typically returns a few thousand rows per "Travel"

Actual Data:
Select [iSim], [dIgnitionON], [dIgnitionOFF] From [iWeb].[dbo].[func_DriverID_IGN_GPS]('361086550,323157632,320156313,323157447,323166668', '10 Jun 2013 22:01', '11 Jun 2013 21:59') Order By iKey

VehicleID       dIgnitionON       dIgnitionOFF
320156313      2013-06-11 07:25:12.400      2013-06-11 07:26:54.953 -- this is (1)
320156313      2013-06-11 07:28:08.230      2013-06-11 07:37:24.777 -- this is (2) below
320156313      2013-06-11 08:13:40.077      2013-06-11 08:30:42.143
320156313      2013-06-11 08:34:07.797      2013-06-11 08:48:17.857
320156313      2013-06-11 08:49:35.030      2013-06-11 08:52:00.877
320156313      2013-06-11 08:57:42.407      2013-06-11 09:00:46.270
320156313      2013-06-11 09:06:37.177      2013-06-11 09:11:30.673
320156313      2013-06-11 09:20:58.500      2013-06-11 09:34:51.870
320156313      2013-06-11 10:28:50.623      2013-06-11 10:44:53.457
..... etc

Function A Result Set for the first, and part of the 2nd, time spans above:
iCntr      VehicleID        dFix      fLatitude       fLongitude
1      320156313      2013-06-11 07:26:02.000      -33.9351600      18.4690567
2      320156313      2013-06-11 07:28:21.000      -33.9350900      18.4687550
2      320156313      2013-06-11 07:28:22.000      -33.9350900      18.4687517
2      320156313      2013-06-11 07:28:23.000      -33.9350867      18.4687467
2      320156313      2013-06-11 07:28:24.000      -33.9350800      18.4687367
2      320156313      2013-06-11 07:28:25.000      -33.9350667      18.4687250
2      320156313      2013-06-11 07:28:26.000      -33.9350483      18.4687117
2      320156313      2013-06-11 07:28:27.000      -33.9350233      18.4686983
2      320156313      2013-06-11 07:28:28.000      -33.9349950      18.4686850
2      320156313      2013-06-11 07:28:29.000      -33.9349567      18.4686700
2      320156313      2013-06-11 07:28:30.000      -33.9349150      18.4686533
2      320156313      2013-06-11 07:28:31.000      -33.9348733      18.4686367
2      320156313      2013-06-11 07:28:32.000      -33.9348300      18.4686217
2      320156313      2013-06-11 07:28:33.000      -33.9347833      18.4686050
2      320156313      2013-06-11 07:28:34.000      -33.9347317      18.4685850
2      320156313      2013-06-11 07:28:35.000      -33.9346783      18.4685650
2      320156313      2013-06-11 07:28:36.000      -33.9346217      18.4685467
2      320156313      2013-06-11 07:28:37.000      -33.9345667      18.4685283
2      320156313      2013-06-11 07:28:42.000      -33.9343100      18.4684017
2      320156313      2013-06-11 07:28:49.000      -33.9339467      18.4682583

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bird757

ASKER

I was not aware of the CROSS APPLY structure - this works wonderfully! Thank you.