Sunday, June 3, 2012

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

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

No comments:

Post a Comment