Parameters in subqueries ?

Can anyone tell me if this is a known bug? When using a parameter in a
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName = ?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName => ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> > Can anyone tell me if this is a known bug? When using a parameter in a
> > subquery I get an access violation
> > The query below reproduces the problem using Northwind.
> >
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> >
> > It also happens when using OLEDB
> >
> > SELECT * FROM Orders
> > WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName
=> > ?)
> >
> > Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> > Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
> Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
>|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion :(
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > I never use the @.param syntax for queries since Enterprise manager does
> > support them.
> Why are you using Enterprise Manager for this?
> > It is not in a stored procedure. It's being submitted from C# code but
it
> > blows up just the same from Enterprise SQL Mangler
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
> > Seems to work ok with named parameters from Query Analyser.
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion :(
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > > I never use the @.param syntax for queries since Enterprise manager does
> > > support them.
> >
> > Why are you using Enterprise Manager for this?
> >
> > > It is not in a stored procedure. It's being submitted from C# code but
> it
> > > blows up just the same from Enterprise SQL Mangler
> >
> > If you're sending the code like that, why not fill in the parameter value
> in
> > C#?
> >
> > > Seems to work ok with named parameters from Query Analyser.
> >
> > As it should. Not all providers are going to understand the same funky
> > syntax that EM requires.
> >
> >
>
>

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:
>