Answered by:
how to use user defined function in view ??

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 .
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
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 -
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.
-
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
-
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
-