functions with parameters sql

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

1SET @OrderID = dbo.GetOrderID (default, default, default);
2

1DECLARE
2	@OrderID int = NULL,
3	@OrderType int = 1,
4	@Qty int = 2,
5	@ServiceSpeed int = 3;
6
7INSERT INTO OrdersTest (OrderType, Qty, ServiceSpeed)
8	VALUES (@OrderType, @Qty, @ServiceSpeed);
9
10EXEC @OrderID = dbo.GetOrderID
11	@OrderType = @OrderType,
12	@Qty = @Qty,
13	@ServiceSpeed = @ServiceSpeed;
14
15SELECT @OrderID 'Using EXEC Syntax';
16

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