Loading in 5 sec....

User-Defined Functions (UDF)PowerPoint Presentation

User-Defined Functions (UDF)

- By
**gomer** - Follow User

- 119 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' User-Defined Functions (UDF)' - gomer

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

### User-Defined Functions (UDF)

User-Defined Functions

What is a User-Defined Function?

- Can have parameters
- Returns a value, either
- A single scalar value
- Unlike a stored procedure
- Most data types are legal

- A table

- A single scalar value
- Can be called from a SELECT statement
- Unlike stored procedures

User-Defined Functions

Syntax and examples

- Syntax
CREATE FUNCTION someName(parameters)

RETURNS someDataType

BEGIN

code

RETURN variable | SELECT statement

END

- Example, returning a scalar/single value
CREATE FUNCTION fnAvgGrade(@studentID int)

RETURNS numeric(3,1)

AS

BEGIN

return (select AVG(grade) from studentCourse where studentID = @studentID)

END

- Calling
- SELECT name, dbo.fnAvgGrade(id) FROM student;

User-Defined Functions

ExampleReturning a table

- Definition
CREATE FUNCTION fnStudentByName(@name varchar(100))

RETURNS TABLE

AS

RETURN

(SELECT * FROM student

WHERE name LIKE'%' + @name + '%');

- Calling
- SELECT * FROM dbo.fnStudentByName('a');

User-Defined Functions

Examplereturning a scalar value

CREATE FUNCTION [dbo].[numberOfEmployeesPrDepartment]

(

@departmentIDint

)

RETURNS int

AS

BEGIN

-- Declare the return variable here

DECLARE @result int

-- Add the T-SQL statements to compute the return value here

SELECT @result = COUNT(*) from teacher where departmentID = @departmentID

-- Return the result of the function

RETURN @result

END

User-Defined Functions

Using a scalar valued function in a CHECK constraint

At most 6 teachers in each department:

CREATE TABLE teacher (

teacherIDint IDENTITY(1,1) PRIMARY KEY,

….

departmentIDint,

CHECK (dbo. numberOfEmployeesPrDepartment( departmentID) <= 6)

)

User-Defined Functions

Download Presentation

Connecting to Server..