Reputation: 33
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
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