Solved

Problem with Declaring a Table Variable within a Table Function

Posted on 2010-08-24
3
239 Views
Last Modified: 2012-05-10
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
Comment
Question by:NathanIrwin
  • 2
3 Comments
 
LVL 11

Expert Comment

by:indianguru2
ID: 33518278
You have to define the Table structure with the Returns keyword and use that to populate and select.
0
 
LVL 11

Accepted Solution

by:
indianguru2 earned 500 total points
ID: 33518284
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
 
LVL 7

Author Closing Comment

by:NathanIrwin
ID: 33518307
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question