Pass a declared variable to a function : function « Procedure Function « SQL Server / T-SQL Tutorial






2>
3> CREATE TABLE Employees (
4>      EmployeeID int NOT NULL ,
5>      LastName nvarchar (20) NOT NULL ,
6>      FirstName nvarchar (10) NOT NULL ,
7>      Title nvarchar (30) NULL ,
8>      TitleOfCourtesy nvarchar (25) NULL ,
9>      BirthDate datetime NULL ,
10>     HireDate datetime NULL ,
11>     Address nvarchar (60) NULL ,
12>     City nvarchar (15) NULL ,
13>     Region nvarchar (15) NULL ,
14>     PostalCode nvarchar (10) NULL ,
15>     Country nvarchar (15) NULL ,
16>     HomePhone nvarchar (24) NULL ,
17>     Extension nvarchar (4) NULL ,
18>     Photo image NULL ,
19>     Notes ntext NULL ,
20>     ReportsTo int NULL ,
21>     PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3>
4> CREATE TABLE RegionPlace
5>      (RegionID nvarchar (20) NOT NULL ,
6>      TerritoryDescription nchar (50) NOT NULL ,
7>         RegionID int NOT NULL
8> )
9> GO
1>
2> Insert Into RegionPlace Values ('01581','Westboro',1)
3> Insert Into RegionPlace Values ('01730','Bedford',1)
4> Insert Into RegionPlace Values ('01833','Georgetow',1)
5> Insert Into RegionPlace Values ('95060','Santa Cruz',2)
6> Insert Into RegionPlace Values ('98004','Bellevue',2)
7> Insert Into RegionPlace Values ('98052','Redmond',2)
8> Insert Into RegionPlace Values ('98104','Seattle',2)
9> Go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> CREATE TABLE EmployeeRegion
2>      (EmployeeID int NOT NULL,
3>       RegionID nvarchar (20) NOT NULL
4> )
5> GO
1>
2> Insert Into EmployeeRegion Values (1,'06897')
3> Insert Into EmployeeRegion Values (1,'19713')
4> Insert Into EmployeeRegion Values (9,'48084')
5> Insert Into EmployeeRegion Values (9,'48304')
6> Insert Into EmployeeRegion Values (9,'55113')
7> Insert Into EmployeeRegion Values (9,'55439')
8> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> CREATE FUNCTION dbo.udf_EmpRegionPlaceTAB (
3>     @EmployeeID int
4> )   RETURNS TABLE
5> AS RETURN
6> SELECT TOP 100 PERCENT WITH TIES
7>        et.RegionID
8>      , t.TerritoryDescription as [Territory]
9>      , t.RegionID
10>     FROM EmployeeRegion et
11>          LEFT OUTER JOIN RegionPlace t
12>              ON et.RegionID = t.RegionID
13>     WHERE et.EmployeeID = @EmployeeID
14>     ORDER BY t.TerritoryDescription
15>
16> GO
1>
2> DECLARE @EmpID int
3> SELECT @EmpID = EmployeeID
4>     FROM Employees
5>     WHERE FirstName = 'Andrew' and LastName = 'Fuller'
6>
7> SELECT * FROM udf_EmpRegionPlaceTAB(@EmpID)
8> GO
RegionID          Territory                                          RegionID
-------------------- -------------------------------------------------- -----------

(0 rows affected)
1>
2>
3>
4> drop FUNCTION dbo.udf_EmpRegionPlaceTAB;
5> GO
1> drop table EmployeeRegion;
2> drop table RegionPlace;
3> drop table Employees;
4> GO
1>








21.2.function
21.2.1.The syntax for creating a multi-statement table-valued function
21.2.2.The syntax for creating a scalar-valued function
21.2.3.The syntax for creating a simple table-valued function
21.2.4.The syntax of the ALTER FUNCTION statement for a scalar valued function
21.2.5.The syntax for altering a simple table-valued function
21.2.6.The syntax for altering a multi-statement table-valued function
21.2.7.Call user-defined function in where clause
21.2.8.Call two user-defined functions in a select statement
21.2.9.Function WITH SCHEMABINDING
21.2.10.Multistatement Table-Valued Functions
21.2.11.Pass a declared variable to a function
21.2.12.Return TOP 100 PERCENT WITH TIES from a function
21.2.13.Get the 3 employees with the most RegionPlace
21.2.14.Use function as a view
21.2.15.Create a scalar-valued function that returns the total Billing amount due
21.2.16.Invoke the scalar-valued function
21.2.17.A SELECT statement that uses the function in a join operation
21.2.18.Use function to wrap a long sql statement
21.2.19.Query a table returned from a function