Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UDF as Computed Column Part 2 Expand / Collapse
Author
Message
Posted Saturday, June 28, 2003 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumnpart2.asp
Post #13692
Posted Monday, July 14, 2003 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2005 4:42 AM
Points: 5, Visits: 1
Dinesh
(As a side effect to the article), thanks for making me aware of the CREATE SCHEMA AUTHORIZATION command.
raj




Post #68641
Posted Monday, July 14, 2003 8:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 15, 2007 9:22 AM
Points: 238, Visits: 1
I also was unaware of the CREATE SCHEMA AUTHORIZATION command thanks for taking a minute to explain it. It will truly come in handy in the future.
Thanks for articles. I have been planning more UDFs since reading your first article and this second one confirms the path I was thinking down.

Thanks,
Ross




Post #68642
Posted Tuesday, July 15, 2003 2:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2003 12:00 AM
Points: 1, Visits: 1
Pardon my ignorance but what is BOL?




Post #68643
Posted Tuesday, July 15, 2003 9:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

Hi noggin,

BOL stand for Books Online

Dinesh


MCP MCSE MCSD MCDBA
Post #68644
Posted Tuesday, July 22, 2003 1:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 2:41 PM
Points: 49, Visits: 42
Thanks for your work. I must have screwed something up but when I ran the 'Create Schema' script I received the following error:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'NULL'.




Post #68645
Posted Tuesday, July 22, 2003 4:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

Hi Currym,
I have tested the script and had not found any error. Let me know the exact code you ran.

Dinesh


MCP MCSE MCSD MCDBA
Post #68646
Posted Wednesday, July 23, 2003 3:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 2:41 PM
Points: 49, Visits: 42
Hmmmm... It was with the first script "Create Schema Authorization" but the entire exercise worked fine this morning. The fn_getDuration function is nice little headbanger.

Thanks for your assistance.




Post #68647
Posted Wednesday, July 14, 2004 4:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 4,576, Visits: 8,348

Sorry, Dinesh, you've got so many titles... Just like number of operands in your script.

But what about performance of your scripts? It's good while it has only 4 rows to work out.

What if you replace your function with this one?

CREATE FUNCTION dbo.fn_getDuration (@STime datetime, @ETime datetime)
RETURNS datetime
AS
BEGIN
 DECLARE @Duration Datetime

 SET @Duration = @ETime-@STime
 RETURN @Duration - floor(convert(real, @Duration))

END

First, it's more precise: it counts not only hours and minutes, but seconds and milliseconds as well. So, it's more usefull;
Second, it works times faster;
And third, it returns datetime value, so you can use it in criteria check for select from big table: WHERE dbo.fn_getDuration (..) > '05:00:00'

Best regards,
Sergiy.

Post #126279
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse