Sign in
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Would you mind listing your function?  You might still be missing an opportunity with this function.
    Monday, February 14, 2011 7:07 PM
    |
    Moderator