CODEWITHSUNDEEP
sql-serversyntax-errorcreate-function
Moeren
Moeren

Reputation: 33

SQL Server : Create Function Wrong Syntax near 'Begin'

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

Upvotes: 0

Views: 275

Answers (1)

Thom A
Thom A

Reputation: 95913

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

Upvotes: 0

Related Questions