none
How to convert into UDF

    Question

  • hi,
    i have a tsql code
    SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20)
    How iwll i change this in to a user defined function so that i could call this everytime i am doing and insert or update.

    Thanks
    Wednesday, January 27, 2010 8:33 PM

Answers

  • Try this

    CREATE FUNCTION dbo.ReturnUser()
    RETURNS VARCHAR(50)
    AS
    BEGIN
    	RETURN SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20)
    END
    
    SELECT dbo.ReturnUser()

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Kent WaldropModerator Friday, January 29, 2010 1:30 PM
    • Marked as answer by KJian_ Wednesday, February 03, 2010 9:24 AM
    Wednesday, January 27, 2010 8:48 PM
  • If you are wanting this established as a function you might want to use this as a inline table function.  A scalar function will optimize badly whereas an inline table function will optimizer pretty well.

    For a simple calculation such as this I would suggest either leaving this as an inline calculation or converting this into a inline table function.

    EDIT:

    create function dbo.returnUser()
    returns table
    as return
    ( select SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20) 
        as Return_User )
    go
    
    select return_User
    from dbo.returnUser()
    
    /* -------- Output: --------
    return_User
    --------------------
    sa
    */
    EDIT:

    Removed some commentary that I decided I didn't like.
    Wednesday, January 27, 2010 9:12 PM

All replies

  • Try this

    CREATE FUNCTION dbo.ReturnUser()
    RETURNS VARCHAR(50)
    AS
    BEGIN
    	RETURN SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20)
    END
    
    SELECT dbo.ReturnUser()

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Kent WaldropModerator Friday, January 29, 2010 1:30 PM
    • Marked as answer by KJian_ Wednesday, February 03, 2010 9:24 AM
    Wednesday, January 27, 2010 8:48 PM
  • Thanks for the help.

    I have one more Q.
    can i use this dbo.ReturnUser() in different db's within the same instance? and if so, would i need to create this function in other db as well?

    Thanks
    Wednesday, January 27, 2010 8:59 PM
  • If you are wanting this established as a function you might want to use this as a inline table function.  A scalar function will optimize badly whereas an inline table function will optimizer pretty well.

    For a simple calculation such as this I would suggest either leaving this as an inline calculation or converting this into a inline table function.

    EDIT:

    create function dbo.returnUser()
    returns table
    as return
    ( select SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20) 
        as Return_User )
    go
    
    select return_User
    from dbo.returnUser()
    
    /* -------- Output: --------
    return_User
    --------------------
    sa
    */
    EDIT:

    Removed some commentary that I decided I didn't like.
    Wednesday, January 27, 2010 9:12 PM
  • Thanks for the help.

    I have one more Q.
    can i use this dbo.ReturnUser() in different db's within the same instance? and if so, would i need to create this function in other db as well?

    Thanks

    Yes, just add the database name to it. For example, if you create it in database ABC, and you want to access it from ABCTest, just do the following

    SELECT ABC.dbo.ReturnUser()
    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, January 27, 2010 9:16 PM
  • I would NOT (mayb never) convert a trivial calculation such as this into a scalar function.  If you are wanting this established as a function I suggest that you use this as a inline table function.  A scalar function will optimize badly whereas an inline table function will optimizer pretty well.

    For a simple calculation such as this I would suggest either leaving this as an inline calculation or converting this into a inline table function.

    EDIT:

    create function dbo.returnUser()
    
    returns table
    
    as return
    
    ( select SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1, 20) 
    
        as Return_User )
    
    go
    
    
    
    select return_User
    
    from dbo.returnUser()
    
    
    
    /* -------- Output: --------
    
    return_User
    
    --------------------
    
    sa
    
    */
    
    
    
    


    Hmmm..  I thought about this one some more.  Second, third opinions please?

    Help?

     

    Wednesday, January 27, 2010 10:10 PM
  • Here's what I found in using SQL Profiler (and doing the usual DBCC commands to start fresh before each and discarding Query Results):

    SELECT SalesOrderID, X=dbo.AbdallahUDF()
    FROM AdventureWorks.Sales.SalesOrderDetail
    /* CPU=1349, Reads=240, Duration=1372 */

    SELECT SalesOrderID, X=(SELECT Return_User FROM dbo.KentUDF())
    FROM AdventureWorks.Sales.SalesOrderDetail
    /* CPU=281, Reads=238, Duration=355 */

    So you're right... your TVF is much more efficient.

    Of course, this would be fastest... call the UDF only once:

    DECLARE @x VARCHAR(20)
    SET @x=dbo.AbdallahUDF()  --or =(SELECT Return_User FROM dbo.KentUDF())
    SELECT SalesOrderID, @x
    FROM AdventureWorks.Sales.SalesOrderDetail
    /* CPU=47, Reads=240, Duration=117 */


    --Brad (My Blog)
    Wednesday, January 27, 2010 10:35 PM
  • The "Call the UDF only once" was what made me wander.  I wasn't sure how it was going to be used.

    I think the more intuitive form is the scalar function.  I admit that the scalar is a bit slower than the inline function, but if it is only going to be called one time I might well prefer the scalar version.  Since there are no arguments to this function it becomes a little more interesting, doesn't it?

    Thank you for picking me up, Brad.

    :-)

    Wednesday, January 27, 2010 10:45 PM
  • I agree... I think for something this simple, with no arguments that have to be passed, then a scalar function is fine.

    --Brad (My Blog)
    Wednesday, January 27, 2010 10:52 PM