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: (3137)

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) 

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

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) ) 
SELECT  empId, empFirstName, empLastName, empCity, empDept
FROM    companyEmployees
WHERE   empName = @employeeName

You can usethis function like this

Select dbo.getEmployeeData(7) as employeeInfo

See other Scripts in Ms sql and database

Submit Your Scripts:

System Message:
  • Your ASP & ASP.NET script has been sent. Please allow 48 hours for processing.

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:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]