Implement Programming Objects: Design T-SQL table-valued and scalar functions

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: ensure code non-regression by keeping consistent signature for procedure, views, and function (interfaces); turn scripts that use cursors and loops into a SET-based operation

What I see:

·         create table-valued functions

·         create scalar functions

Create Table-Valued Functions

                With a table-valued function, the return type is of TABLE data type (as opposed to a scalar return type for a scalar function).  Below is an example that creates a test table, and a table-valued function to select from the table and add a column value.  Note the function definition containing RETURNS TABLE:

use MeasureMySkills;

go

create table TestTableUDF

(

       id int identity(1, 1) not null,

       SomeString nvarchar(128) not null

              default replicate('a', 128),

       SomeNum int not null

);

go

insert into TestTableUDF(SomeNum)

values(10), (20), (30);

go

create function TblValFunc

(

       @add_num int

)

returns table

as

       return

       (

              select

                     id,

                     SomeString,

                     SomeNum,

                     SomeNum + @add_num as AddedSomeNum

              from TestTableUDF

       );

go

select *

from TblValFunc(5);

Create Scalar Functions

                As opposed to a table-valued function, a scalar function returns a single value.  Instead of a RETURN TABLE clause, we now have a RETURN [DataType].  In this example, we have a return type of integer:

create function ScalarFunc

(

       @original_num int,

       @add_num int

)

returns int

as

begin

       return (@original_num + @add_num);

end

go

select

       *,

       dbo.ScalarFunc(SomeNum, 5) as AddedSomeNum

from TestTableUDF;

References

·         BOL reference on CREATE FUNCTION

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.