Solved

Problem with Declaring a Table Variable within a Table Function

Posted on 2010-08-24
3
238 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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