Re: User Defined Functions ???
From: Hari (hari_prasad_k_at_hotmail.com)
Date: 02/26/04
- Next message: Fabrizio Maccarrone: "sp_executesql"
- Previous message: Ray Higdon: "Re: User Defined Functions ???"
- In reply to: Martin: "User Defined Functions ???"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Feb 2004 17:48:26 +0530
Hi,
There are 3 types of UDF in sql server
1. Scalar Functions
2. Inline Table-valued Funtions
3. Multi statement table valued Functions
I assume if i created a function ConvertX i would use it the same way as
the Convert function in a SQL statement?
Ans: Yes, But UDF can be used in From clause. Refer the examples below.
Are the UDFs table or database level?
Database level and can be used with linked servers as well.
Just refer to SQL Server Books online . You have lots of examples.
Examples from books online on different types of UDF
A. Scalar-valued user-defined function that calculates the ISO week
In this example, a user-defined function, ISOweek, takes a date argument and
calculates the ISO week number. For this function to calculate properly,
SET DATEFIRST 1 must be invoked before the function is called.
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
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
Here is the function call. Notice that DATEFIRST is set to 1.
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
Here is the result set.
ISO Week
----------------
51
B. Inline table-valued function
This example returns an inline table-valued function.
USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
C. Multi-statement table-valued function
Given a table that represents a hierarchical relationship:
CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)
The table-valued function fn_FindReports(InEmpID), which -- given an
Employee ID -- returns a table corresponding to all the employees that
report to the given employee directly or indirectly. This logic is not
expressible in a single query and is a good candidate for implementing as a
user-defined function.
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO
-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
Thanks
Hari
MCDBA
"Martin" <anonymous@discussions.microsoft.com> wrote in message
news:238c01c3fc60$a2d40610$a401280a@phx.gbl...
> Hi,
>
> Can someone tell me or point me in the right direction
> regarding user defined functions ? Within Ent Manager I
> can't find an example or help within the MS product.
>
> I thought Master or Northwind woul have one!
>
> How do create one?
>
> I assume if i created a function ConvertX i would use it
> the same way as the Convert function in a SQL statement?
>
> Are the UDFs table or database level?
>
> Thanks
- Next message: Fabrizio Maccarrone: "sp_executesql"
- Previous message: Ray Higdon: "Re: User Defined Functions ???"
- In reply to: Martin: "User Defined Functions ???"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
