Function Issue - - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Function Issue -

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-07-07 : 09:50:38

Hi All,

I am writing a function to get the sum of Duration(time) from a History table. The function is passed 4 parameters and the output is a single value. I keep getting errors -

Msg 156, Level 15, State 1, Procedure udf_MCP_Flt_getDuration_History, Line 31
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Procedure udf_MCP_Flt_getDuration_History, Line 31
Incorrect syntax near ')'.

My function is -
Create FUNCTION [dbo].[udf_MCP_Flt_getDuration_History]
(
-- Add the parameters for the function here
@intMCPID int,
@FltType varchar(10),
@SOSTimeFrom datetime,
@SOSTimeTo datetime

)
RETURNS int
AS
BEGIN

declare @SQL1 varchar(max);
declare @mins int;

set @SQL1 ='Select sum( dbo.ConvertTImeToMin( Duration ))
from PRT_MCPX_Flt_History
where MCPID = ' + CONVERT (varchar(3),@intMCPID) + '
and Fault like ''%'+ @FltType + '00'''
+ ' and Occurred>='''+ CONVERT (varchar(19), @SOSTimeFrom) + ''''
+ ' and Occurred<='''+ CONVERT (varchar(19), @SOSTimeTo) + ''''

set @mins = (exec sp_executesql @SQL1)
return (@mins)

END

--select [dbo].[udf_MCP_Flt_getDuration_History](1,'CommFlt', '2010-06-08 10:35:34.447', '2010-07-06 10:35:34.447' )

The table PRT_MCPX_Flt_History -

CREATE TABLE [dbo].[PRT_MCPX_Flt_History](
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
) ON [PRIMARY]

Data in PRT_MCPX_Flt_History -

MCP01CommFlt01 2010-06-08 10:35:34.447 00:15:00 1
MCP01CommFlt00 2010-06-24 16:57:22.000 00:17:29 1
MCP01CommFlt01 2010-07-03 13:43:46.000 00:17:28 1
MCP01CommFlt02 2010-07-03 13:43:48.000 00:10:40 1
MCP01CommFlt03 2010-07-03 13:43:50.000 00:10:38 1
MCP01CommFlt04 2010-07-03 13:43:51.000 00:10:36 1
MCP01CommFlt05 2010-07-03 13:43:52.000 00:10:34 1
MCP01PanelFlt00 2010-06-24 16:57:22.000 00:06:42 1
MCP01PanelFlt01 2010-07-03 13:54:39.000 00:06:42 1
MCP01PanelFlt02 2010-07-03 13:54:39.000 00:06:41 1
MCP01MotorFlt00 2010-06-24 16:57:22.000 00:06:08 1
MCP01MotorFlt01 2010-07-03 13:54:45.000 00:06:07 1
MCP01MotorFlt02 2010-07-03 13:54:45.000 00:06:06 1
MCP01PEJam00 2010-06-24 16:57:22.000 00:05:53 1
MCP01PEJam01 2010-07-03 13:54:52.000 00:05:53 1
MCP01PEJam02 2010-07-03 13:54:52.000 00:05:52 1
MCP01EStop02 2010-07-03 13:55:02.000 00:05:36 1
MCP01CommFlt03 2010-07-03 13:56:52.000 00:04:22 1
MCP01PanelFlt00 2010-07-05 09:12:03.000 00:03:12 1
MCP01PanelFlt01 2010-07-05 21:13:01.000 00:03:12 1
MCP01PanelFlt02 2010-07-05 21:13:00.000 00:03:12 1
MCP01PanelFlt03 2010-07-05 21:13:01.000 00:03:10 1
MCP08PanelFlt00 2010-07-05 09:12:03.000 00:03:11 8
MCP08PanelFlt01 2010-07-05 21:13:05.000 00:03:11 8
MCP08PanelFlt02 2010-07-05 21:13:06.000 00:03:10 8
MCP08PanelFlt03 2010-07-05 21:13:07.000 00:03:08 8
MCP01PanelFlt00 2010-07-05 09:22:16.000 00:25:49 1
MCP01PanelFlt01 2010-07-05 21:23:07.000 00:00:57 1
MCP01MotorFlt03 2010-07-03 13:54:46.000 00:06:05 1
MCP01EStop00 2010-07-03 13:55:01.000 00:05:37 1
MCP01EStop01 2010-07-03 13:55:00.000 00:05:38 1
MCP01CommFlt02 2010-07-03 13:56:52.000 00:00:16 1
MCP01CommFlt04 2010-07-03 13:56:53.000 00:00:13 1
MCP01CommFlt05 2010-07-03 13:56:54.000 00:02:30 1
MCP01CommFlt06 2010-07-03 13:56:55.000 00:02:29 1
MCP01CommFlt07 2010-07-03 13:56:56.000 00:02:28 1
MCP01CommFlt08 2010-07-03 13:56:56.000 00:02:27 1
MCP01CommFlt04 2010-07-03 13:56:58.000 00:02:27 1
MCP01CommFlt02 2010-07-03 13:57:11.000 00:02:15 1
MCP01PanelFlt02 2010-07-05 21:23:08.000 00:25:47 1
MCP01PanelFlt03 2010-07-05 21:23:10.000 00:25:46 1
MCP01PanelFlt01 2010-07-05 21:24:38.000 00:24:17 1

Any ideas ?

Thanks,

AlanPBates
Starting Member

34 Posts

Posted - 2010-07-07 : 10:08:39

Sorry Guys - I fgiured it out .. It was simply structuring the SQL - This works -

return (Select sum( dbo.ConvertTImeToMin( Duration ))
from PRT_MCPX_Flt_History
where MCPID = CONVERT (varchar(3),@intMCPID)
and Fault like + '%'+ @FltType + '00'
and Occurred>= CONVERT (varchar(19), @SOSTimeFrom)
and Occurred<=CONVERT (varchar(19), @SOSTimeTo))

I was not reading properly ..

Thanks !