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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s