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

Posted on 2011-05-07
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

    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

    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 24

    Assisted Solution

    by:DBAduck - Ben Miller
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now