select query as function parameter

Question

Post reply

select query as function parameter

Charbel-371270

SSCrazy

Points: 2260
More actions
August 10, 2007 at 2:24 am

#118159

hi
i have a function and i want to pass the parameter from a select query
like this

select ID from dbo.functionName ( select name from table where id=1)
i get this error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'
so its not possible to do that?
If something's hard to do, then it's not worth doing.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

As far as I know, that isn't allowed. Something like this should work:

DECLARE

@name SYSNAME

SELECT

@name = [name]

FROM

WHERE

[id] = 1

SELECT

[ID]

FROM

dbo.functionName(@name)

John

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 2

yes john its the most clear way to do it

i also did it

but i was just wondering if a query can be written in the way i provided or its not possible to use a select query as a parameter in a function

10x

If something's hard to do, then it's not worth doing.

hr_sn Ten Centuries Points: 1072 More actions · Answer 3

I think you can do this in SQL 2005 using cross join or cross apply and pass field value to function, but at the same I think your function should return table instead of scalar value. Check BOL for more detail if you've got 2005.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 4

The only thing you can pass to a function are scalar values... can't pass a query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 5

thats the strict answer i was waiting

10x

If something's hard to do, then it's not worth doing.

Lowell SSC Guru Points: 323504 More actions · Answer 6

you could keep it scalar by doing something like this:

select ID, dbo.functionName (name) from table where id=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 7

Now, there's some common sense thinking outside the box... thanks, Lowell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 8

cross apply(oops only 2005) But I'll leave the post.

From BOL

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

As an example, consider the following tables, Employees and Departments:

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

This works if the function returns a scalar, but not if it returns a table.