Table Function is asking me to declare a variable multiple times.

[SACRob]

The following is a function i am trying to create to reformat a a one line dataset into a multi row table.
However it is asking me to redeclare the returned variable multiple times in T-SQL so i am sure my syntax is off slightly.

USE [GP1]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_workinghour]    Script Date: 03/30/2009 20:45:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fn_holidayconversion]
(
    @address_ID Varchar(16),
	@cust Varchar(16)
)
returns @DATE table 
(
	[day_number] [varchar](50) NOT NULL,
	[day_name] [varchar](50) NULL,
	[begin_time] [datetime] NULL,
	[end_time] [datetime] NULL,
	[duration] [real] NULL
)
as
begin
declare @address Varchar(16)
declare @customer Varchar(16)
declare @DATE Table([day_number] [varchar](50) NOT NULL,
	[day_name] [varchar](50) NULL,
	[begin_time] [datetime] NULL,
	[end_time] [datetime] NULL,
	[duration] [real] NULL)
select @address=@address_ID
select @customer=@cust
insert into @DATE
SELECT '1' as 'day_number','Monday' as 'day_name',workstrt_1 as 'begin_time',workend_1 as 'end_time',datediff(s,workstrt_1,workend_1) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '2' as 'day_number','Tuesday' as 'day_name',workstrt_2 as 'begin_time',workend_2 as 'end_time',datediff(s,workstrt_2,workend_2) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '3' as 'day_number','Wednesday' as 'day_name',workstrt_3 as 'begin_time',workend_3 as 'end_time',datediff(s,workstrt_3,workend_3) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '4' as 'day_number','Thursday' as 'day_name',workstrt_4 as 'begin_time',workend_4 as 'end_time',datediff(s,workstrt_4,workend_4) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '5' as 'day_number','Friday' as 'day_name',workstrt_5 as 'begin_time',workend_5 as 'end_time',datediff(s,workstrt_5,workend_5) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '6' as 'day_number','Saturday' as 'day_name',workstrt_6 as 'begin_time',workend_6 as 'end_time',datediff(s,workstrt_6,workend_6) as 'duration'
  FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
union all
SELECT '7' as 'day_number','Sunday' as 'day_name',workstrt_7 as 'begin_time',workend_7 as 'end_time',datediff(s,workstrt_7,workend_7) as 'duration'
FROM [GP1].[dbo].[SVC00950] where custnmbr=@customer and ADRSCODE=@address
return @DATE table
end

 

[SACRob]

I get the error Msg 134, Level 15, State 1, Procedure fn_holidayconversion, Line 18
The variable name '@DATE' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 137, Level 15, State 2, Procedure fn_holidayconversion, Line 46
Must declare the scalar variable "@DATE".

which is where i am declaring the table @DATE and returning the table at the end.

 

[gmmastros]

Change:

return @DATE table
end

To:

Return
End

You don't need the @DATE table


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom