[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-05-07
Medium Priority
Last Modified: 2012-05-11
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?

Question by:Bird757
LVL 11

Accepted Solution

JoeNuvo earned 1000 total points
ID: 35711761
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   )
	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 .......

Open in new window

another way, using dynamic query.

Author Comment

ID: 35711841
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.
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1000 total points
ID: 35712596
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.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question