how to use user defined function in view ??

All replies

• 

  

  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

  Reply

  |

  Quote

  Moderator
• 

  

  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

  Reply

  |

  Quote

  Moderator
• 

  

  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

  Reply

  |

  Quote
• 

  

  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

  Reply

  |

  Quote

  Moderator
• 

  

  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

  Reply

  |

  Quote

  Moderator