Products
Services
Contact Us

Scripts: asp :: MS SQL and Database :: Library Article #6

Developer's Section

MS SQL User Defined Functions
By: Erobo Team Member

Hire a Developer for Related Work / Installation | $55 hr
Rating:  | Rate It:   
Average Votes: (1851)
Favorites:

Learn to use MS SQL user defined functions.

In this tutorial we will cover two common types of functions used in MS SQL. The first one named scalar functions which usually return a value (varchar, int, decimal) etc...

The second type are inline table user defined functions which return a complete table instead of just a value. You can use both types of functions to retrieve information from a table using the SELECT, WHERE or CASE SQL statements.

Scalar UDFs Example: Get table rows into a Single String

 Code Snippet 1

CREATE FUNCTION dbo.getAffData(@thisId int) 
RETURNS VARCHAR(1000)  
AS 

BEGIN 
    DECLARE @res VARCHAR(1000); 
    SET @res = ''
    SELECT @res = @res + j.affiliatedNames + ', '  
    FROM  dbo.affiliates j,  dbo.companyRecords c 
    WHERE c.id = @thisId and j.deptid = c.id;
    IF (LEN(@res) > 0) 
        SET @res = SUBSTRING(@res,1,LEN(@res)-1)
    RETURN @res;
END


You can use this function like this:

Select dbo.getAffData(7) as affiliatesString



Inline Table UDFs Example: Get data from a table

 Code Snippet 2

CREATE FUNCTION dbo.getEmployeeData
    ( @employeeName VARCHAR(40) ) 
RETURNS TABLE 
RETURN 
SELECT  empId, empFirstName, empLastName, empCity, empDept
FROM    companyEmployees
WHERE   empName = @employeeName
go

You can usethis function like this

Select dbo.getEmployeeData(7) as employeeInfo


See other Scripts in MS SQL and Database

Submit Your Scripts:

If you would like to have your ASP & ASP.NET scripts published in this section please fill out
the form below:
*Your Name or Username:
Home Town:
*Email:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]