• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Problem with Declaring a Table Variable within a Table Function

I am getting an
Incorrect syntax near the keyword 'DECLARE'
Error with them following Table Function. Anyone see anything obvious? been a while since I have needed to use Table Functions

Thanks
Nathan
CREATE FUNCTION dbo.Mist_States_for_Interval
(
--Function parameters
	@startshift datetime,
	@endshift datetime
)
RETURNS TABLE
AS
BEGIN
(
-- Create the Table Variable
DECLARE @States_For_Interval TABLE
(id integer, Equipment_id integer, Status_Id integer ,reason_id integer, location_id integer, starttime datetime, endtime datetime, Duration integer)

.....Do some stuff...

UPDATE @States_For_Interval
SET Duration = datediff(ss,@starttime,@endshift)
WHERE starttime = (SELECT MAX(endtime) FROM @States_For_Interval)


SELECT * FROM @States_For_Interval
ORDER BY starttime
	
RETURN
END
)

Open in new window

CREATE FUNCTION dbo.Mist_States_for_Interval
(
--Function parameters
	@startshift datetime,
	@endshift datetime
)
RETURNS TABLE
AS
BEGIN

-- Create the Table Variable
DECLARE @States_For_Interval TABLE
(id integer, Equipment_id integer, Status_Id integer ,reason_id integer, location_id integer, starttime datetime, endtime datetime, Duration integer)

...do some stuff..

UPDATE @States_For_Interval
SET Duration = datediff(ss,@starttime,@endshift)
WHERE starttime = (SELECT MAX(endtime) FROM @States_For_Interval)


SELECT * FROM @States_For_Interval
ORDER BY starttime
	
RETURN
END

Open in new window

0
NathanIrwin
Asked:
NathanIrwin
  • 2
1 Solution
 
indianguru2Commented:
You have to define the Table structure with the Returns keyword and use that to populate and select.
0
 
indianguru2Commented:
Attached is the SQL Server Template 
CREATE FUNCTION <Table_Function_Name> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

Open in new window

0
 
NathanIrwinAuthor Commented:
Thanks,
I tried that before, but was getting another error, I must have resolved that one because it is working now.

Cheer,
Nathan
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now