Parameterized UDF in correlated subquery

Hello all,
I'm trying to run a query that looks like this:
UPDATE MyTable SET
ParsedField1 = (SELECT ParsedField1 FROM dbo.parseField(ot.FieldToParse)),
ParsedField2 = (SELECT ParsedField2 FROM dbo.parseField(ot.FieldToParse)),
ParsedField3 = (SELECT ParsedField3 FROM dbo.parseField(ot.FieldToParse)),
ParsedField4 = (SELECT ParsedField4 FROM dbo.parseField(ot.FieldToParse)),
FROM MyTable mt
INNER JOIN OtherTable ot ON mt.pid = ot.pid
<FieldtoParse> is a string that requires some fairly complicated logic to
parse so I encapsulated it in a UDF that returns a table resultset. However
,
using a parameterized UDF in a correlated subquery appears to be unsupported
.
It thinks it's a HINT! Can someone tell me if I'm right about that?
I have several other options I can use to get the same thing accomplished so
I'm not worried about that but this way would have been the cleanest.
Thanks for the help!
MikeWhy not to create a scalar function instead?
UPDATE MyTable SET
ParsedField1 = dbo.parseField(ot.FieldToParse, 1),
ParsedField2 = dbo.parseField(ot.FieldToParse, 2),
ParsedField3 = dbo.parseField(ot.FieldToParse, 3),
ParsedField4 = dbo.parseField(ot.FieldToParse, 4),
FROM MyTable mt
INNER JOIN OtherTable ot ON mt.pid = ot.pid
go
AMB
"Mike L" wrote:

> Hello all,
> I'm trying to run a query that looks like this:
> UPDATE MyTable SET
> ParsedField1 = (SELECT ParsedField1 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField2 = (SELECT ParsedField2 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField3 = (SELECT ParsedField3 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField4 = (SELECT ParsedField4 FROM dbo.parseField(ot.FieldToParse)
),
> FROM MyTable mt
> INNER JOIN OtherTable ot ON mt.pid = ot.pid
> <FieldtoParse> is a string that requires some fairly complicated logic to
> parse so I encapsulated it in a UDF that returns a table resultset. Howev
er,
> using a parameterized UDF in a correlated subquery appears to be unsupport
ed.
> It thinks it's a HINT! Can someone tell me if I'm right about that?
> I have several other options I can use to get the same thing accomplished
so
> I'm not worried about that but this way would have been the cleanest.
> Thanks for the help!
> Mike
>|||I can and did actually create one very similar to this. However, my
question is more academic. I haven't found anything saying this is
prohibited but it doesn't appear to work. I was just wondering why or if
there's a syntax "trick" you have to use to get it to work.
Thanks for the reply though!
Mike
"Alejandro Mesa" wrote:
> Why not to create a scalar function instead?
> UPDATE MyTable SET
> ParsedField1 = dbo.parseField(ot.FieldToParse, 1),
> ParsedField2 = dbo.parseField(ot.FieldToParse, 2),
> ParsedField3 = dbo.parseField(ot.FieldToParse, 3),
> ParsedField4 = dbo.parseField(ot.FieldToParse, 4),
> FROM MyTable mt
> INNER JOIN OtherTable ot ON mt.pid = ot.pid
> go
>
> AMB
> "Mike L" wrote:
>|||Mike
Can you show us an UDF? I'm pretty sure that Alejandro answers your
question.
"Mike L" <MikeL@.discussions.microsoft.com> wrote in message
news:230EE401-C561-45F6-8396-0F9E9E071A0D@.microsoft.com...
> I can and did actually create one very similar to this. However, my
> question is more academic. I haven't found anything saying this is
> prohibited but it doesn't appear to work. I was just wondering why or if
> there's a syntax "trick" you have to use to get it to work.
> Thanks for the reply though!
> Mike
> "Alejandro Mesa" wrote:
>