SQL Server/T-SQL Tutorial/Procedure Function/Utility function
Содержание
Changing Character Values to Proper Case with user-defined functions
3>
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> CREATE FUNCTION dbo.udf_ProperCase(@UnCased varchar(max))
4> RETURNS varchar(max)
5> AS
6> BEGIN
7> SET @UnCased = LOWER(@UnCased)
8> DECLARE @C int
9> SET @C = ASCII("a")
10> WHILE @C <= ASCII("z")
11> BEGIN
12> SET @UnCased = REPLACE( @UnCased, " " + CHAR(@C), " " + CHAR(@C-32))
13> SET @C = @C + 1
14> END
15> SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased,LEN(@UnCased)-1)
16> RETURN @UnCased
17> END
18> GO
1>
2> SELECT dbo.udf_ProperCase(first_name)
3> FROM employee
4> GO
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
Jason
Alison
James
Celia
Robert
Linda
David
James
Joan
(9 rows affected)
1>
2> drop function dbo.udf_ProperCase
3> GO
1> drop table employee;
2> GO
1> --Reference from:
2> --SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
3>
Format a date
3> CREATE FUNCTION MyDateFormat
4> (@indate datetime,
5> @Separator char(1)="-")
6> RETURNS nchar(20)
7> AS
8> BEGIN
9> RETURN
10> CONVERT(nvarchar(20), DATEPART(dd, @indate))
11> + @Separator
12> + CONVERT(nvarchar(20), DATEPART(mm, @indate))
13> + @Separator
14> + CONVERT(nvarchar(20), DATEPART(yy, @indate))
15> END
16> GO
1>
2> SELECT dbo.MyDateFormat(GETDATE(), "*")
3> GO
--------------------
17*8*2008
1>
2>
3> drop FUNCTION MyDateFormat;
4> GO
if an int odd
4> CREATE FUNCTION dbo.ufnIsOdd (@n int = 5)
5> RETURNS bit
6> AS
7> BEGIN
8> RETURN (@n % 2)
9> END
10> GO
1>
2> CREATE PROCEDURE usp_NumberIsOdd
3> @n int = 5
4> AS
5> SELECT @n "Number", @n % 2 "Is_odd = 1"
6> GO
1>
2> DECLARE @numb int
3>
4> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1"
5> SELECT @numb "Number", dbo.ufnIsOdd (DEFAULT) "Is_odd = 1"
6> SET @numb = 6
7> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1"
8> GO
Number Is_odd = 1
----------- ----------
NULL NULL
(1 rows affected)
Number Is_odd = 1
----------- ----------
NULL NULL
(1 rows affected)
Number Is_odd = 1
----------- ----------
6 0
(1 rows affected)
1>
2> drop FUNCTION dbo.ufnIsOdd;
3> GO
1>
2> drop PROCEDURE usp_NumberIsOdd;
3> GO
linear_max() Scalar Function
6> CREATE FUNCTION dbo.linear_max
7> (
8> @arg1 AS int,
9> @arg2 AS int
10> )
11> RETURNS int
12> AS
13> BEGIN
14> RETURN CASE
15> WHEN @arg1 >= @arg2 THEN @arg1
16> WHEN @arg2 > @arg1 THEN @arg2
17> ELSE NULL
18> END
19> END
20> GO
1>
2> SELECT
3> dbo.linear_max(1, 2)
4>
5> drop function dbo.linear_max
6> GO
-----------
2
(1 rows affected)
1>
2>
Scalar Functions
A scalar function accepts any number of parameters and returns one value.
Input parameters are declared within parentheses followed by the return value declaration.
All statements must be enclosed in a BEGIN. . . END block.
8>
9> CREATE FUNCTION fnGetAge (@BirthDate DateTime, @Today DateTime)
10> RETURNS Int
11> AS
12> BEGIN
13> RETURN DateDiff(day, @BirthDate, @Today) / 365.25
14> END
15> GO
1>
2>
3> drop function fnGetAge;
4> GO
1>
The linear_min Scalar Function
4> CREATE FUNCTION dbo.linear_min(
5> @arg1 AS int,
6> @arg2 AS int
7> )
8> RETURNS int
9> AS
10> BEGIN
11> RETURN CASE
12> WHEN @arg1 <= @arg2 THEN @arg1
13> WHEN @arg2 < @arg1 THEN @arg2
14> ELSE NULL
15> END
16> END
17> GO
1> GRANT EXECUTE ON dbo.linear_min TO public
2> GO
1>
2> drop function dbo.linear_min
3> GO