How to pass column values directly to a SQL Table Function that is expecting Variables?

I have a Table-Valued function that requires 4 variables as inputs. These inputs exist in another user table.

Can I Do a direct select from FunctionX(Var1, Var2, Var3, Var4) Where these 4 Variables are a row from the user table, without first assigning the Row to 4 @Variables?

Example - The Function Call looks like this (and returns a number of rows):
Select * From [dbo].[func_Array_Hour](1, 111, '22 Apr 2011 16:00', '23 Apr 2011 04:00')

The 4 Variables exist in a different Table, so I would normally:
Declare @Var1 Int, @Var2 Int, @Var3 VarChar(20), @Var4 VarChar(20)
Select @Var1 = [iKey], @Var2 = [iControl], @Var3 = [dStart], @Var4 = [dEnd] From [dbo].[UserTable] Where [UserTable].[iKey] = 1

And then I would make the Function Call:
Select * From [dbo].[func_Array_Hour](@Var1, @Var2, @Var3, @Var4)

I have tried (but it does not work):
Select * From [dbo].[func_Array_Hour]([iKey], [iControl], [dStart], [dEnd]) From [dbo].[UserTable] Where [UserTable].[iKey] = 1

I have also tried:
Select * From [dbo].[func_Array_Hour]((Select [iKey], [iControl], [dStart], [dEnd] From [dbo].[UserTable] Where [UserTable].[iKey] = 1))

Is there a way to implement this without the intermediate step of first assigning the row to variables?

Thanks
Bird757Asked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
I could think of 2 possible ways to go

first way, send "1" as single variable into function, and uses it to query other 4 required variable inside function
for ex
CREATE FUNCTION <FunctionName> ( @iKey int )
RETURNS <@Table_Variable_Name> TABLE 
(  Add the column definitions for the TABLE variable here   )
AS
BEGIN
	Declare @Var1 Int, @Var2 Int, @Var3 VarChar(20), @Var4 VarChar(20)
	Select @Var1 = [iKey], @Var2 = [iControl], @Var3 = [dStart], @Var4 = [dEnd]
	From [dbo].[UserTable]
	Where [UserTable].[iKey] = @iKey

	-- Fill the table variable with the rows for your result set
	INSERT INTO <@Table_Variable_Name>
	SELECT .......
	
	RETURN 
END

Open in new window


another way, using dynamic query.
0
 
Bird757Author Commented:
If I am understanding this correctly it is not possible to supply the column names as inputs to the function?

The example suggested still amounts to a 2-step process where the variables are assigned (although this is happening in a function), and the secondary call then uses the assigned variables.

I use columns directly on Scalar Function calls and was hoping the same could be achieved when the function is a Table Function.
0
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
That is correct.  You have to have the values or elude to the values so that the function can get them.

So in your case, you could do it the way you were, by getting the 4 values before calling the function, and that is the way it should be if you are going to use that function as a generic function.  

If you are going to use it tightly coupled to the table that has the 4 values in it, then you can just pass the key in as illustrated above and get the values while in the function.

There is no way to combine them the way you would like to, even though if there was, you could argue that it is still a 2 step process, just indirectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.