user defined function sql

1CREATE FUNCTION <function_name> (<@paramater_name>    <datatype>)
2RETURNS <return_datatype>
3AS
4BEGIN
5 	<DML statements>
6    RETURN expression
7END
8
9        

1USE tempdb;
2GO
3
4DROP FUNCTION IF EXISTS dbo.GetOrderID;
5DROP TABLE IF EXISTS OrdersTest;
6GO
7
8CREATE TABLE OrdersTest (OrderID int IDENTITY, OrderType int, Qty int, ServiceSpeed int);
9GO
10
11CREATE FUNCTION GetOrderID
12(
13	@OrderType int = 0,
14	@ServiceSpeed int = 0,
15	@Qty int = 0
16)
17RETURNS int AS
18BEGIN
19	RETURN
20	(
21		SELECT
22			TOP 1 OrderID
23		FROM		OrdersTest
24		WHERE		OrderType = @OrderType
25		AND			ServiceSpeed = @ServiceSpeed
26		AND			Qty = @Qty
27		
28	);
29END
30GO
31

1EXEC @OrderID = dbo.GetOrderID
2	@OrderType = @OrderType,
3	@ServiceSpeed = @Qty,
4	@Qty = @ServiceSpeed;
5

1SET @var_name = expr [, @var_name = expr] ...

1Collation is defined as a set of rules
2that determine how data can be sorted 
3as well as compared. Character data is
4sorted using the rules that define the 
5correct character sequence along with
6options for specifying case-sensitivity,
7character width etc.