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
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