May 27, 2010 at 4:27 am
Hi all!
I'm having quite a strange problem, in every other sql server version it works, but this one version throws an error when I'm trying to run a normal select for a function. And the story goes:
1) I have a table-valued function which takes two parameters (which I call "functionName" here). 1st param is of type char(4) and the 2nd param is of type int.
2) I get the 1st param for the function from a table's (I call it "table") column (I call it "field") with inner select while calling the function.
3) As you can see below with the example, I have even simplified this select so that I removed all the parameters (both the '1's are really parameters):
SELECT * FROM dbo.functionName((SELECT field FROM table WHERE id=1), 1)
4) When I run it with some other version I get success, but with two different instances of version 9.0.4723 I get error like this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
What on earth?!?! Am I missing something or what the heck is going on?! If someone has an idea I would be reallythankful.
Best Regards,
-- Arto Kainu
May 27, 2010 at 4:49 am
Arto Kainu (5/27/2010)
Hi all!I'm having quite a strange problem, in every other sql server version it works, but this one version throws an error when I'm trying to run a normal select for a function. And the story goes:
1) I have a table-valued function which takes two parameters (which I call "functionName" here). 1st param is of type char(4) and the 2nd param is of type int.
2) I get the 1st param for the function from a table's (I call it "table") column (I call it "field") with inner select while calling the function.
3) As you can see below with the example, I have even simplified this select so that I removed all the parameters (both the '1's are really parameters):
SELECT * FROM dbo.functionName((SELECT field FROM table WHERE id=1), 1)4) When I run it with some other version I get success, but with two different instances of version 9.0.4723 I get error like this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
What on earth?!?! Am I missing something or what the heck is going on?! If someone has an idea I would be reallythankful.
Best Regards,
-- Arto Kainu
Table valued functions shouldn't have a problem with this syntax in any version, but this single line programming isn't quite my favourite. For readability issues I prefer something like this:
DECLARE @field CHAR(4)
SELECT @field = field FROM table WHERE id=1
SELECT * FROM dbo.functionName(@field, 1)
This way, you might be better able to bugfix this issue.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
May 27, 2010 at 5:41 am
Hi Ronald,
Thank You for your participation in finding ot the problem. Yes, I know what you mean, and I actually could fix it with that kind of syntax. But what if this query is inside another SELECT which selects many rows and this select should be made on each row internally? Then it would need a cursor or some other way to get the correct value for each row. Then this syntax helps a lot.
But, now that I have gone deeper to the problem, there is something very wrong with the sql server query engine which reads the query syntax.
Any nested select creates the Incorrect syntax error. Here's another example from the WHERE clause where the error occurs:
SELECT * FROM table a WHERE ...
and
(
(@varA<> 2 AND @varB IS NULL)
or (@varA <> 2 AND a.fieldA=@varB)
or (@varA = 2 AND a.fieldA IN(SELECT fieldB FROM @otherTableVar))
)...
This IN syntax is not easily replaceable, or does someone figure out another easy way?
Could there be some kind of t-sql syntax options which could be changed? I really am confused with this one...
Thank You again for all of you helping me out.
- Arto Kainu
May 27, 2010 at 11:04 pm
Arto,
I do vaguely remember something like this being a bug - I'll see if I can track the Connect item down.
In any case, it seems fixed in the latest 2005 code (I'm running 9.0.4285).
Please try the following reproduction script:
USE tempdb;
GO
CREATE FUNCTION dbo.CircleArea
(
@p0 DOUBLE PRECISION
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT @p0 * @p0 * PI() AS [pr²];
GO
DECLARE @T
TABLE (
row_id INTEGER IDENTITY (1,1) NOT NULL PRIMARY KEY,
radius DOUBLE PRECISION NOT NULL
);
INSERT @T (radius) VALUES (05);
INSERT @T (radius) VALUES (12);
INSERT @T (radius) VALUES (37);
-- Query form 1
SELECT F.[pr²]
FROM dbo.CircleArea ((SELECT T.radius FROM @T T WHERE T.row_id = 1)) F;
-- Query form 2
SELECT F.[pr²]
FROM (SELECT T.radius FROM @T T WHERE T.row_id = 1) DrivingSet
CROSS
APPLY dbo.CircleArea (DrivingSet.radius) F;
-- Query form 3
WITH DrivingSet
AS (
SELECT T.radius
FROM @T T
WHERE T.row_id = 1
)
SELECT F.[pr²]
FROM DrivingSet
CROSS
APPLY dbo.CircleArea (DrivingSet.radius) F;
GO
DROP FUNCTION dbo.CircleArea;
I would be interested to hear if any of those query forms fail to work on your engine version.
Paul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply