Problem calling a function within a SELECT
-
I'm having trouble calling a function within a SELECT statement.
To start off with this works (simple function call):
SELECT
(
dbo.GetAssessmentStatusCode(S.ScheduleID, 20361)
) AS 'All status codes'
FROM AframeSchedule S
But this code below does not compile. It says:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'S'.
SELECT
(
SELECT COUNT (CompletionStatus)
FROM dbo.GetAssessmentStatusRows(1920, S.ScheduleID )
WHERE CompletionStatus = 'TARGET_REACHED'
) AS 'Total number of people who have reached target score'
FROM AframeSchedule S
If I then remove the offending "S.ScheduleID" and replace it with a constant number it works fine (but that's not what I want).
SELECT
(
SELECT COUNT (CompletionStatus)
FROM dbo.GetAssessmentStatusRows(1920, 1924)
WHERE CompletionStatus = 'TARGET_REACHED'
) AS 'Total number of people who have reached target score'
FROM AframeSchedule S
Any help REALLY appreciated !
Thanks in advance,
Ian
-
Read the link Sergiy posted, it should explain the situation.
To be more specific, source of error is here:
FROM dbo.GetAssessmentStatusRows(1920, S.ScheduleID )
You are using a function instead of table. If this is a table valued function, it returns table and you could refer to it as to a table, but ... you can not allow any variability in the function parameters. There must be precisely one value for each parameter. With S.ScheduleID as a parameter, you have a problem - there could be several ScheduleID's in the underlying table, and for each of them the function would return one separate table. Then your select would need to have multiple tables in the FROM clause, which is impossible. Because of this, SQL Server does not allow usage of anything other than explicit values... like 1924. You have to find some other way to accomplish your task (unfortunately you didn't mention what it is, so I have no idea how to do it).
-
Thanks for the replys people.
I'm now marginally wiser.
-
fyi, what you're trying to do does work in SQL 2005, with some minor mods. It's possible in 2005 to do the following
SELECT * FROM SomeTable S CROSS APPLY dbo.SomeFunction(S.aField) F
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply