2779B Module 9 - Implementing Functions
Three kinds of functions
Scalar
Function
-returns a single value
-RETURNS clause specifies data type returned
-Function def is in a BEGIN END block
-can be invoked anywhere on a scalar expression of the same data type is allowed.
-can’t use nondeterministic function calls within the scalar function. Why?
Google
Groups answer:
Likewise, when you call a scalar UDF, SQL (the
language) is supposed to act
as if the UDF is being applied simultaneously to each row in the table.
Obviously in reality this is not how things really work, but for SQL to be
consistent with its mathematical foundations this is how it is supposed to
look. The problem with the getdate( ) function
in particular is that because
in reality a scalar
UDF is applied row-by-row, if the UDF calls the
getdate( ) function then the UDF may very well produce different
results
than the theory demands.
Another
way to look at it ... as before, a UDF is supposed to be applied to
each row of a table simultaneously. Thus if you have a table with a column
c1, and every row of the table has the value 6, then you would expect, and
SQL (the language) demands, that the scalar UDF return the same value for
every row of the table. Simply put, given the same input, you expect to get
the same output. Reasonable enough, right? But if the scalar UDF calls the
getdate(
) function, then
there is a chance that your calculations within
the UDF will depend upon the value returned by the getdate(
) function, and
thus, because in reality, a scalar
UDF in SQL Server is currently applied
row-by-row, the return value from the UDF may differ row-by-row even though
the input was the same.
Also:
What you could do is send
GETDATE() as a param.
Syntax:
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS
int
WITH
EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek
int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as
CHAR(4))+'0104')
--Special
cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+
CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special
case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>=
28))
SET @ISOweek=1
RETURN(@ISOweek)
END;
GO
SET
DATEFIRST 1;
--Sets
the first day of the week to a number from 1 through 7.
SELECT
dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
ISO Week
----------------
52
Inline
table-valued function
-returns a table
-used like a view with parameters
-format is defined by a select statement
-definition of the function is a select statement
Syntax:
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID,
P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM
JOIN
JOIN
WHERE SH.CustomerID
= @storeid
GROUP BY P.ProductID,
P.Name
);
GO
-- SELECT * FROM Sales.ufn_SalesByStore (602);
Multi statement table
valued function (MSTVF)
-Returns a table as a result of insert statements
-more flexable than inline…
-the table that a MSTVF creates has to be defined within the function, its not just the output of a single select statement, it can be the result of any one of many select statements (thus the multi).
-you define the table variable, then decide which select statement to choose and insert it’s output into the variable which is returned.
Syntax:
--
Create Sales.GetDiscountedProducts
CREATE FUNCTION Sales.GetDiscountedProducts (@IncludeHistory bit)
RETURNS
@tbl_products
TABLE
(ProductID
int,
Name nvarchar(50),
ListPrice
money,
DiscountDescription
nvarchar(255),
DiscountPercentage
smallmoney,
DiscountAmount
money,
DiscountedPrice
money)
AS
BEGIN
IF (@IncludeHistory
= 1)
INSERT @tbl_products
SELECT P.ProductID,
P.Name,
P.ListPrice,
SO.Description,
SO.DiscountPct,
P.ListPrice * SO.DiscountPct,
P.ListPrice - P.ListPrice * SO.DiscountPct
FROM Sales.SpecialOfferProduct
SOP INNER JOIN
Sales.SpecialOffer SO ON SOP.SpecialOfferID
= SO.SpecialOfferID INNER JOIN
Production.Product P ON SOP.ProductID
= P.ProductID
WHERE (SO.DiscountPct
> 0)
ORDER BY ProductID
ELSE
INSERT @tbl_products
SELECT P.ProductID,
P.Name,
P.ListPrice,
SO.Description,
SO.DiscountPct,
P.ListPrice * SO.DiscountPct,
P.ListPrice - P.ListPrice * SO.DiscountPct
FROM Sales.SpecialOfferProduct
SOP INNER JOIN
Sales.SpecialOffer SO ON SOP.SpecialOfferID
= SO.SpecialOfferID INNER JOIN
Production.Product P ON SOP.ProductID
= P.ProductID
WHERE (SO.DiscountPct
> 0) AND GetDate() BETWEEN StartDate AND EndDate –note that you can use a nondeterministic function
call here
ORDER BY ProductID
RETURN
–returns the value of @tbl_products
END
GO
--
Test Sales.GetDiscountedProducts
SELECT
* FROM Sales.GetDiscountedProducts(0)
SELECT
* FROM Sales.GetDiscountedProducts(1)
GO
Structured Exception Handling Woo Hoo!
-consists of try catch blocks
Create procedure adddata @a int, @b int
AS
BEGIN TRY
..
END TRY
BEGIN CATCH
..
END CATCH
-Error Handling occurs here
Execution Context
-this was in the review on module 8 in marks videos (see notes there too)
-user goes to execute a statement and doesn’t have the perms.
- You can execute as
-CALLER: The user executing the stored procedure
CREATE
PROC DisplayContext
WITH EXECUTE AS CALLER
AS SELECT
CURRENT_USER AS USERNAME
exec DisplayContext –returns dbo when
logged in a student since student has sysadmin perms
-SELF: The user creating or altering the stored procedure
-OWNER: The owner of the stored procedure
CREATE
PROC GetOrders
WITH EXECUTE AS OWNER
AS SELECT
* FROM Sales.order
-A specified user
CREATE
PROC GetOrds
WITH EXECUTE AS ‘Pat’
AS SELECT * rom Sales.Ord
- if the user in this database is mapped to a user in another database you can run that stored procedure as that user in a different database.
- Login Creation Syntax
CREATE LOGIN [MIAMI\Adam]
FROM WINDOWS
WITH DEFAULT_DATABASE = Adventureworks
CREATE USER Adam FOR
LOGIN [MIAMI\Adam]
-It’s a good idea to rewrite stored procedures as functions if it will improve performance