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

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

Submit Your Scripts: