Functions and tble name as parameter

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)

Functions and tble name as parameter

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-21 : 14:11:04

I have 200 Tables containing data from various equipment. The prefix [MCP1] for the table (eg - MCP1_Estop) defines the equipment number and the Suffix (eg _Estop or _Jam or MotorFlt) defines the type of the fault.

I need to write a function which is passed the TableName and it will output the Duration. The duration is a particular fault which ends in 00.

Typically data looks like -
'MCP1_EStop00', '04-30-2010 18:35:34.447', '10:00', 1
'MCP1_EStop02', '04-29-2010 12:31:34.447', '1:15', 1
'MCP1_EStop03', '04-27-2010 18:04:34.447', '3:15', 1
'MCP1_EStop01', '04-30-2010 05:05:34.447', '4:15', 1
'MCP1_EStop03', '04-30-2010 02:45:34.447', '2:15', 1

TABLE[s] -
CREATE TABLE [dbo].[MCP1_EStop](
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL )

My Current Function is -

Create FUNCTION [dbo].[udf_MCP1_EStop_getDuration]
(
-- Add the parameters for the function here
@intMCPID int

)
RETURNS time
AS
BEGIN
-- Declare the return variable here
DECLARE @MCPId time

-- Add the T-SQL statements to compute the return value here

set @MCPId = (select Duration from MCP1_EStop
where MCPID = @intMCPID and Fault like ('%00'))
-- Return the result of the function
RETURN @MCPId

END

I want to write the function such that the input parameter is the table name like .. MCP1_Estop or MCP7_Jam or MCP49_MotorFlt etc etc .. and the output is the duration from the row containing "00" eg - 'MCP1_EStop00', '04-30-2010 18:35:34.447', '10:00', 1
with just the time - "10"

Can anyone help me here ? I have to use the function refer to any of the 200 tables for writing out reports.

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 14:35:48

Since dynamic SQL is not allowed in a function, you'll need to write a series of IF/SELECT statements:

IF @tableName = 'Table1'
SELECT * FROM Table1
ELSE IF @tableName = 'Table2'
...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-21 : 14:39:04

But when select from 200 tables .. won't it take time ?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 14:42:09

There is no alternative as dynamic SQL is not allowed in functions.

Change your database design so that it is normalized to avoid writing code like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-21 : 14:43:32

Unfortunately .. this was designed by someone else .. I am just adding functionality ..

Thanks Tara ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources