RESOLVED: Stored Procedure of Function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

RESOLVED: Stored Procedure of Function?

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-16 : 11:20:08

I have a select statement like this. Notice that I am doing the same calculation on different fields. Is it possible I can make this, in coding terms, a fucntion; so I can call it when I like (this query) and just provide the field.

Example: select field1, test_field = secs_to_hhmmss(TALK_TIME) from ...

SELECT dbo.date_table.real_date, dbo.time_table.hh,
COUNT(dbo.CALLDETAIL.id) as NumOfCalls,
TalkTime =
CASE WHEN CAST(SUM(TALK_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(TALK_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) % 60),2),
HoldTime =
CASE WHEN CAST(SUM(HOLD_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(HOLD_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) % 60),2),

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 11:21:37

you mean you provide the column name and the function works it's magic on that column? no you can't.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-16 : 11:47:53

NVM

CREATE function fn_SplitWords (@intNumber integer) returns integer AS
BEGIN
declare @Answer integer
set @Answer = @intNumber / 10
-- return @Answer
return @Answer
end

CREATE function dbo.fn_AspectTimeConvert (@intSeconds integer) returns varchar(12) AS
BEGIN
declare @Answer varchar(12)
set @Answer = CASE WHEN CAST(SUM(@intSeconds) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(@intSeconds) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(@intSeconds) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(@intSeconds) AS INTEGER) % 3600) % 60),2)
return @Answer
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-16 : 11:51:37

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources