Solved

Problem with Declaring a Table Variable within a Table Function

Posted on 2010-08-24
3
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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