SQL Server Performance Forum – Threads Archive

Compare Views and User Defined Function

Hi ; I have a senario in which I have to create three views b/c few joining tables are changed on the basis of one parameter. Now, I am thinking to merge them into on UDF using Parameter. Now, I have two question: 1- Which is better to use either Views or Function with respect to performance. 2- B/c I can’t create parameter into Views so I had to create three views for the same stored procedure and I am calling them on the basis of different paramenter value. Now, I have to create the function which can take input parameter and give me different result set on the basis of my input values and I will use that function into my stored procedure. The problem is that I am trying to use the following syntax but it is not working. ********************************** Create Function fn_GetContact
( @Param varchar(20) = ‘A’ )
Returns table As Return (
If @Param = ‘A’
Select * from tblNameA
Else If @Param = ‘B’
Select * from tblNameB
Else
Select * from tblNameC
) ********************************** Any help will be appreciated. Thanks. Essa, M. Mughal
Software Engineer
Canada

---

Create Function fn_GetContact
( @Param varchar(20) = ‘A’ )
Returns @Contacts table
(column1 column1Type,
column2 column2type,
….
columnN columnNtype
) As
if @Param = ‘A’ insert into @Contacts Select column1, …, columnN from tblNameA
Else
If @Param = ‘B’ insert into @Contacts Select column1, …, columnN from tblNameB
Else insert into @Contacts Select column1, …, columnN from tblNameC Return
For table function syntax and examples see BOL. You can also use Query Analiser template for creatign new table function.

---

Read about the difference between multi-statement table-valued functions and inline table-valued functions. — Rediscover the web
http://www.mozilla.org/firefox/

---

]]>