Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Oct 16, 2001

Using SQL Server 2000 User-Defined Functions to Retrieve Table-Like Data


In the SQL Server 7 days, when we wanted to calculate data and return it, we usually used stored procedures. User-defined functions are a new feature of SQL Server and they are much better as their results can be used in the 'From' clause of a 'Select' statement. Imagine that you wanted to retrieve data from the Shoes or Shirts tables in order to determine their stock in a flexible way.
 
Create Function SomeTable(@Name char(6))
Returns @recordsExtracted Table (quantityInStock)
As
Begin
If @Name='Shoes' Then
Insert @recordsExtracted Select quantityInStock From Shoes
If @Name='Shirts' Then
Insert @recordsExtracted Select quantityInStock From Shirts
Return
End

As you can see, logic and returned recordset in one place. To use this function, you would do the following:
 
Select Sum(quantityInStock) From SomeTable('Shoes') , or,
Select Sum(quantityInStock) From SomeTable('Shirts')
Jorge Silva
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date