select statement as a function parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select statement as a function parameter

Author

Topic

captainjon
Starting Member

4 Posts

Posted - 2008-08-29 : 09:31:18

Hi,
I'm using a function in the middle of a join to make an XML document,
and I need to pass a scalar to the function which produces a table result.

i.e. select * from dbo.mySplitter(select 'jon:was:here')

The partial query is here

SELECT [column].field,

(SELECT * FROM reportdetails as column2
CROSS APPLY
dbo.QSSplitter(column2.link) as fn
WHERE column2.field = [column].field -- join with itself
AND column2.id = [column].id

FOR XML RAW, TYPE, ELEMENTS, ROOT('action'))

FROM reportdetails [column]
FOR XML AUTO, TYPE, ROOT('table'))

Here is my attempt to use CROSS APPLY but it gives me
'Incorrect syntax near '.'' on dbo.QSSplitter(column2.link).

I cant use an exec in a function, or create a temporary table...
Any Ideas??

thanks,
Jon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:35:23

Why just "partial" query?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:39:59

Rewrite QSSplitter function to accept three parameters and try this

SELECT		rd.Field,
		fn.Column
FROM		ReportDetails AS rd
CROSS APPLY	dbo.QSSplitter(Link, ID, Field) AS fn
FOR XML		AUTO,
		TYPE,
		ROOT('table')

E 12°55'05.63"
N 56°04'39.26"

captainjon
Starting Member

4 Posts

Posted - 2008-08-29 : 10:42:09

thanks I got cross apply to work. I didnt have 2005 capatibility on the database! Doh!

captainjon
Starting Member

4 Posts

Posted - 2008-08-29 : 10:42:49

(SELECT
[column].field ,

(SELECT fn.* FROM reportdetails column2 CROSS APPLY
dbo.QSSplitter(column2.link) as fn
WHERE column2.field = [column].field
AND column2.id = [column].id

FOR XML RAW, TYPE, ELEMENTS, ROOT('action'))

FROM reportdetails [column]
WHERE report.id = [column].id
FOR XML AUTO, TYPE, ROOT('table'))

captainjon
Starting Member

4 Posts

Posted - 2008-08-29 : 10:44:55

What alternatives are there to use a select query as a scalar parameter to a function?

thanks,
Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources