Use function as a view : function « Procedure Function « SQL Server / T-SQL Tutorial

3> CREATE TABLE Products (
4>      ProductID int NOT NULL ,
5>      ProductName nvarchar (40) NOT NULL ,
6>      SupplierID int NULL ,
7>      CategoryID int NULL ,
8>      QuantityPerUnit nvarchar (20) NULL ,
9>      UnitPrice money NULL,
10>     UnitsInStock smallint NULL,
11>     UnitsOnOrder smallint NULL,
12>     ReorderLevel smallint NULL,
13>     Discontinued bit NOT NULL
14> )
15> GO
1> INSERT Products VALUES(1,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products VALUES(2,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products VALUES(3,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products VALUES(4,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products VALUES(5,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products VALUES(6,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products VALUES(7,'O',12,2,'12 boxes',13,23,0,15,0)
8> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>    CREATE PROC spMarkupTest
3>       @MarkupAsPercent money
4>    AS
5>       DECLARE @Multiplier money
6>    SELECT @Multiplier = @MarkupAsPercent / 100 + 1
7>    SELECT TOP 10 ProductId, ProductName, UnitPrice,
8>       UnitPrice * @Multiplier AS "Marked Up Price", "New Price" =
9>       CASE WHEN FLOOR(UnitPrice * @Multiplier + .24)
10>                  > FLOOR(UnitPrice * @Multiplier)
11>                          THEN FLOOR(UnitPrice * @Multiplier) + .95
12>            WHEN FLOOR(UnitPrice  * @Multiplier + .5) >
13>                  FLOOR(UnitPrice * @Multiplier)
14>                          THEN FLOOR(UnitPrice * @Multiplier) + .75
15>            ELSE FLOOR(UnitPrice * @Multiplier) + .49
16>       END
17>    FROM Products
18>    ORDER BY ProductID DESC
19>                              
20>    GO
1>
2>    EXEC spMarkupTest 10
3>
4>    drop PROC spMarkupTest;
5>
6>
7>    drop table Products;
8>    GO
ProductId   ProductName                              UnitPrice             Marked Up Price       New Price
----------- ---------------------------------------- --------------------- --------------------- ----------------------
          7 O                                                      13.0000               14.3000                14.4900
          6 L                                                      18.0000               19.8000                19.9500
          5 R                                                       1.2300                1.3530                 1.4900
          4 L                                                      10.0000               11.0000                11.4900
          3 R                                                      17.0000               18.7000                18.7500
          2 M                                                      34.8000               38.2800                38.4900
          1 F                                                      61.5000               67.6500                67.7500

(7 rows affected)

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