SQL Server/T-SQL/Store Procedure Function/Create Function
Содержание
Define function to get last name
<source lang="sql">
14> 15> CREATE FUNCTION fnName (@Name VarChar(100)) 16> RETURNS VarChar(100) 17> AS 18> BEGIN 19> DECLARE @CommaPosition Int, @LastName varchar(100) 20> SET @CommaPosition = 6 21> SET @LastName = SUBSTRING(@Name, 1, @CommaPosition) 22> RETURN @LastName 23> END 24> GO Msg 2714, Level 16, State 3, Server sqle\SQLEXPRESS, Procedure fnName, Line 22 There is already an object named "fnName" in the database. 1> 2> SELECT dbo.fnName("Washington, George") 3> GO
Washin (1 rows affected) 1> 2> drop function fnName 3> GO
</source>
Define user function and use it in select statement
<source lang="sql">
1> 2> CREATE FUNCTION dbo.ufnIsOdd (@n int) 3> RETURNS bit 4> AS 5> BEGIN 6> RETURN (@n % 2) 7> END 8> GO 1> 2> DECLARE @numb int 3> SET @numb = 6 4> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1" 5> SET @numb = 7 6> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1" 7> 8> GO Number Is_odd = 1
----------
6 0
(1 rows affected) Number Is_odd = 1
----------
7 1
(1 rows affected) 1>
</source>
Inline Table-Valued Functions
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> -- Inline Table-Valued Functions 3> 4> CREATE FUNCTION myProc(@ID Int) 5> RETURNS Table 6> AS 7> RETURN 8> ( 9> SELECT ID, Name, Salary FROM Employee 10> WHERE ID = @ID 11> ) 12> GO 1> SELECT * FROM myProc(1) 2> GO ID Name Salary
---------- -----------
1 Jason 40420
(1 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
Multi-Statement Table-Valued Functions
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> -- Multi-Statement Table-Valued Functions 3> 4> CREATE FUNCTION myProc (@ID Int) 5> RETURNS @EmployeeList Table 6> ( ID Int 7> , Name nVarChar(50) 8> , Salary Money 9> ) 10> AS 11> BEGIN 12> IF @ID IS NULL 13> BEGIN 14> INSERT INTO @EmployeeList (ID, Name, Salary) 15> SELECT ID, Name, Salary 16> FROM Employee 17> END 18> ELSE 19> BEGIN 20> INSERT INTO @EmployeeList (ID, Name, Salary) 21> SELECT ID, Name, Salary 22> FROM Employee 23> WHERE ID = @ID 24> END 25> RETURN 26> END 27> GO 1> 2> select * from myProc(1) 3> GO ID Name Salary
-------------------------------------------------- ---------------------
1 Jason 40420.0000
(1 rows affected) 1> 2> drop function myProc 3> drop table employee 4> GO 1>
</source>
