Single Select Statement as Multiple Parameters

Question

I have a Function that allows 2 parameters:

SELECT * FROM dbo.MyFunction( 'Value1', 'Value2' )

I need to pass the parameters by using a sql statement:

SELECT * FROM dbo.MyFunction
( 
    ( SELECT Column1 FROM MyTable WHERE Rec = 1 ), 
    ( SELECT Column2 FROM MyTable WHERE Rec = 1 ) 
)

Since the 2 Select Statements are referencing the same table, I was wondering if it's possible to pass the values through a single select statement something to this effect:

SELECT * FROM dbo.MyFunction( ( SELECT Column1, Column2 FROM MyTable WHERE Rec = 1 ) )

Is something like that even possible? It has to remain as a single statement as above, using Variables are unfortunately not possible otherwise I could easily do this:

DECLARE @Var1 nvarchar(20)
DECLARE @Var2 nvarchar(20)
SELECT @Var1 = Column1, @Var2 = Column2 FROM MyTable WHERE Rec = 1
SELECT * FROM dbo.MyFunction( @Var1, @Var2 )

The Script is inputted into a parsing engine for a web application but it only supports a basic SELECT function as the script gets ripped apart and then put back together to prevent SQL Injection Attacks so we're very limited in what we're able to do. The First and Second scripts above work perfectly, I just wanted to see if there was a way to get the Third script to work optimize the script.

Appreciate any assistance.

Damien_The_Unbeliever · Accepted Answer · 2013-07-09 07:16:58Z

3

You can use APPLY:

SELECT r.* --TODO - Pick columns
FROM
   MyTable t
     CROSS APPLY dbo.MyFunction(t.Column1,t.Column2) as r
WHERE
   t.Rec = 1

answered Jul 9, 2013 at 7:16

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Storm Over a year ago

Hi, Sorry for the late reply, I was rushed to meetings for two days straight. This is such an awesome answer, I absolutely love it. While I haven't yet had chance to study it in detail or study the documentation, I will be doing so tonight, I just wanted to first stop and say thank you :)

Storm Over a year ago

Hi Damien, I just managed to get time this weekend to properly study the APPLY functionality and apply it to my scenario, I managed to reduce the end result from 7 individual SQL Statements into just 1, awesome stuff. Thanks again!

Collectives™ on Stack Overflow

Single Select Statement as Multiple Parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related