SQL Server : Create Function Wrong Syntax near 'Begin'

Question

I have a problem figuring out how to get rid of an error. It says there is wrong Syntax near the Begin statement. I assume it means before, but I do not know what. I've tried many different declarations of the function but did not get it to work.

I've table that is feeded a line in every step of a process, for multiple processes. The function should take a process name (unit) and time and should result all lines for that process from start to end.

Executing the sql without a function works fine.

CREATE FUNCTION [GetFullCIP]
(
 @pTime DATETIME2,
 @pName NVARCHAR(50)
)
RETURNS TABLE 
AS
BEGIN
 DECLARE @cipid int
 SELECT TOP(1) @cipid=unit_id FROM [dbo].[md_units] WHERE unit=@pName

 DECLARE @stop Datetime2;
 DECLARE @start Datetime2;

--start
 SELECT TOP (1) @start=[begin_date]   FROM [dbo].[log] WHERE [operation_id]=1  AND unit_id=@cipid AND [begin_date]   <=@pTime ORDER BY [cip_id] DESC
--stop
 SELECT TOP (1) @stop=[inserted_date] FROM [dbo].[log] WHERE [operation_id]=99 AND unit_id=@cipid AND [inserted_date]>=@pTime ORDER BY [cip_id] ASC

 
 RETURN (SELECT * FROM [dbo].[log] WHERE unit_id=@cipid AND [begin_date]>=@start AND [inserted_date]<=@stop)
END
GO

I read that i should give the return table a name, like @resdata. I tried that and at the end write SET @resdata=(SELECT ...) but that doesnt work, by than it does not know @resdata anymore.

Thx in advance

Does this answer your question? [Declare variable in table valued function](https://stackoverflow.com/questions/6661505/declare-variable-in-table-valued-function) i.e. I don't think `returns table` is the right variety of declaration if you have multiple statements. — underscore_d, Feb 16 '21 at 12:25
Personally, I would suggest using an inline table-value function anyway, not a multi-line table-value function; the former are far more performant. — Thom A, Feb 16 '21 at 12:32
@underscore_d In that link it only uses a single value. I have multiple rows with multiple values. Thats why i tried to put them into @ resdata with SET. Now I tried it with SELECT INTO but it still doesn't know what resdata is, despite: ...RETURNS @ resdata TABLE ( [cip_id] [int], [inserted_date] [datetime2](2), ... — Moeren, Feb 16 '21 at 12:58
@Lamu i didn't write many functions in sql before so that question may be dumb but if its inline, i assume it works on a row to row base.. but i need to look at the lines before and after und result multiple in between. — Moeren, Feb 16 '21 at 13:01
If you want to ping someone, @Moeren , you have to spell their alias correctly. And I have no idea what you mean by the above in terms of "on a row by row base". — Thom A, Feb 16 '21 at 13:19
Time to read the documentation. With a multi-statement UDF, you must define the table to be returned in the function signature along with the structure of that table. Laziness here will not work. That will fix the first syntax error but you will then find another error. And that is your code must populate your table since the function returns that table. You can find example by searching or by simply reading the documentation. — SMor, Feb 16 '21 at 13:34
@Smor U mean like RETURNS @ resdata TABLE ( cipid [int], .. ) AS BEGIN .. SELECT cipid [int], .. INTO @ resdata FROM .. ? I already tried that, but it still says wrong syntax near @ resdata for the select into statement. — Moeren, Feb 16 '21 at 13:47
Because you did not read the documentation and you are simply throwing more code at the wall hoping something sticks and compiles. "select .... into ..." creates a table, does it not? But the table to be returned is effectively created within the function signature - you just need to populate it. — SMor, Feb 16 '21 at 17:20

score 0 · Accepted Answer · answered Feb 16 '21 at 13:58

As I mentioned, I would use an inline table-value function. This is untested, due to no sample data or expected results, but is a literal translation of the ml-TVF you have posted.

CREATE FUNCTION dbo.[GetFullCIP] (@pTime datetime2(7), @pName nvarchar(50))
RETURNS table
AS RETURN

    SELECT L.* --Replace this with your explicit columns
    FROM dbo.log L
         JOIN dbo.md_units MU ON L.unit_id = MU.unit_id
    WHERE MU.Unit = @pName
      AND L.begin_date >= (SELECT TOP (1) sq.begin_date
                           FROM dbo.log sq
                           WHERE sq.operation_id = 1
                             AND sq.unit_id = MU.unit_id
                             AND sq.begin_date <= @pTime
                           ORDER BY sq.cip_id DESC)
      AND L.inserted_date <= (SELECT TOP (1) sq.inserted_date
                              FROM dbo.log sq
                              WHERE sq.operation_id = 99
                                AND sq.unit_id = MU.unit_id
                                AND sq.inserted_date >= @pTime
                              ORDER BY sq.cip_id ASC)
GO

I had this solution in mind, but i do not know how good sql Server at caching is. The log table is increadibly huge and i wanted to avoid using the same query twice if possible. — Moeren, Feb 16 '21 at 14:33
What query twice, @Moeren ? There's no repetition in the above. — Thom A, Feb 16 '21 at 14:33
Sorry, it seems i was somewhere else with my mind. That actually looks good. Thank you, i will try it.. but still i wonder why the other did not work — Moeren, Feb 16 '21 at 14:38
*"i wonder why the other did not work"* Smor already covered that in their [comment](https://stackoverflow.com/questions/66224214/sql-server-create-function-wrong-syntax-near-begin/66225768?noredirect=1#comment117083646_66224214), @Moeren . — Thom A, Feb 16 '21 at 14:48

SQL Server : Create Function Wrong Syntax near 'Begin'

1 Answers1