Error: Incorrect syntax near the keyword 'DECLARE'

Transact-SQL




Can someone tell me why I am getting this error:
Msg 156, Level 15, State 1, Procedure fn_FunctionName, Line 11
Incorrect syntax near the keyword 'DECLARE'

CREATE FUNCTION fn_FunctionName(@ElementID int)
RETURNS TABLE
AS
RETURN
--create a table (o) to hold the outcomes returned by fn_GetDescendantsByClass
DECLARE @OUTCOMES TABLE (ID int, PARENT_ID int, CLASS_ID int, TITLE nvarchar(255))
INSERT @OUTCOMES
SELECT *
FROM fn_GetDescendantsByClass(@ElementID, 302)

SELECT DISTINCT sl.JOB_ID, o.ID
FROM @OUTCOMES o
INNER JOIN
(SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID
FROM SCENE_LINK
WHERE PROPERTY_ID IN (97, 151)) AS sl
ON o.ID = sl.START_SCENE_ID
GO

Thanks very much

Top

Re: Error: Incorrect syntax near the keyword 'DECLARE'

Konstantin Kosinsky


What do you want return from you function

If you want return results of:

SELECT DISTINCT sl.JOB_ID, o.ID
FROM @OUTCOMES o
INNER JOIN
(SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID
FROM SCENE_LINK
WHERE PROPERTY_ID IN (97, 151)) AS sl
ON o.ID = sl.START_SCENE_ID

Try following T-SQL:

CREATE FUNCTION fn_FunctionName(@ElementID int)

RETURNS TABLE

AS

RETURN

SELECT DISTINCT sl.JOB_ID, o.ID

FROM fn_GetDescendantsByClass(@ElementID, 302) o

INNER JOIN

(SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID

FROM SCENE_LINK

WHERE PROPERTY_ID IN (97, 151)) AS sl

ON o.ID = sl.START_SCENE_ID

GO





Top

Re: Error: Incorrect syntax near the keyword 'DECLARE'

rknowles

Of course...
That is a much better solution.
I don't know why I was trying to make it so difficult. I had moved this code over from a stored procedure, and was so focused on replacing the SELECT-EXEC statement that was originally used that I didn't realize I could use the new function (fn_GetDescendantsByClass) just like a table in my query.

Thank you so much.

Just for my education...Do you know what the problem was with my syntax at the DECLARE statement

Thank you, again.



Top

Re: Error: Incorrect syntax near the keyword 'DECLARE'

Konstantin Kosinsky

You code didn't work because you mixed Inline Table-valued Functions and Multistatement Table-valued Functions.

For inline you don't need to define result structure, but body of function could consist only RETURN statement and one SELECT statement. Inline tvf sometimes called "view with parameters"

For multistatement you could write few statement in function body, but you must include these statement into BEGIN .. END block. Also you need to define result table struct in RETURNS statement.


Top

Re: Error: Incorrect syntax near the keyword 'DECLARE'

rknowles

Thank you for your reply

I will continue playing with this example so I can be sure that I understand everything you just stated.



Top