none
how to use user defined function in view ?? RRS feed

  • Question

  • Hi all,

    I have a function that returns the name of a server that we use ,for using that function i need to declare 2 variables

    declare @k varchar(10), @s varchar(5)

    set @k=@@servername

    exec @s= exec function @k

    @s has the name of the serer that we use ,,

    so depending upon the name of the server the VIEW should populate

    say i have 4 coulmns in view ,, depending upon server the column gets populated ,, so my question is how to use this function , parameter in VIEW

    Thanks .

    Monday, February 14, 2011 5:25 PM

Answers

  • No, you can use it like this

     

    create view ViewA
    
    as
    
    select case F.Result when 'server1' then id1
    
    when 'server2' then id2 else id3 end as ID
    
    from Table1 
    
    CROSS APPLY (select dbo.FunctionName(@@SERVERNAME) as Result) F
    

    I used CROSS APPLY technique to avoid repeating, but if you change your CASE expression to use the form I showed, then

    select case dbo.FunctionName(@@SERVERNAME) when 'Server1' then ID1 etc -- in this form we only called this function once.

    Keep in mind, that using SCALAR T-SQL UDF makes performance bad.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by uddu Monday, February 14, 2011 6:46 PM
    Monday, February 14, 2011 6:38 PM
    Moderator

All replies

  • Are you sure it's a function and not a stored procedure?

    If this is a table valued function, then

    select * from dbo.FunctionName(@@ServerName) -- will return a table defined in that function

    If your function is a scalar valued function, then

    declare @Result varchar(10)

    select @Result = dbo.FunctionName(@@SERVERNAME)

    select * from ViewName where Server = @Result


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, February 14, 2011 5:32 PM
    Moderator
  • Hi all,

    I have a function that returns the name of a server that we use ,for using that function i need to declare 2 variables

    declare @k varchar(10), @s varchar(5)

    set @k=@@servername

    exec @s= exec function @k

    @s has the name of the serer that we use ,,

    so depending upon the name of the server the VIEW should populate

    say i have 4 coulmns in view ,, depending upon server the column gets populated ,, so my question is how to use this function , parameter in VIEW

    Thanks .


    If you are executing the "function" as you say, it is a stored procedure.  The short answer to your question about including it in a view is that you must not do so directly.  However, if the stored procedure does not update anything and does not violate any of the other constraints that are placed on functions (see books online for details of this information), try rewriting the stored procedure as a function.

    In addition, it is best to write this new function as an inline function if possible for best performance.

    Monday, February 14, 2011 5:56 PM
    Moderator
  • Hi ,

    function is scarlar valued function ,,

    can i use like this

    create view viewA
    as
    
    declare @Result varchar(10)
    
    select @Result = dbo.FunctionName(@@SERVERNAME) 
    
    select case WHEN @result='server1' THEN id1
             WHEN @result='server2' THEN id2 else id3 end asID
    from table 1

    because i need to use the result of fucntion

    Thanks

    Monday, February 14, 2011 6:33 PM
  • No, you can use it like this

     

    create view ViewA
    
    as
    
    select case F.Result when 'server1' then id1
    
    when 'server2' then id2 else id3 end as ID
    
    from Table1 
    
    CROSS APPLY (select dbo.FunctionName(@@SERVERNAME) as Result) F
    

    I used CROSS APPLY technique to avoid repeating, but if you change your CASE expression to use the form I showed, then

    select case dbo.FunctionName(@@SERVERNAME) when 'Server1' then ID1 etc -- in this form we only called this function once.

    Keep in mind, that using SCALAR T-SQL UDF makes performance bad.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by uddu Monday, February 14, 2011 6:46 PM
    Monday, February 14, 2011 6:38 PM
    Moderator
  • Would you mind listing your function?  You might still be missing an opportunity with this function.
    Monday, February 14, 2011 7:07 PM
    Moderator