devxlogo

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

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)AsBeginIf @Name='Shoes' ThenInsert @recordsExtracted Select quantityInStock From ShoesIf @Name='Shirts' ThenInsert @recordsExtracted Select quantityInStock From ShirtsReturnEnd


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')

See also  5 Ways to Improve Customer Experience
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist