-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path12_70 Kitchen sink Orders Dynamic version.sql
55 lines (45 loc) · 1.42 KB
/
12_70 Kitchen sink Orders Dynamic version.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
USE [WideWorldImporters]
GO
/****** Object: StoredProcedure [dbo].[OrdersSearch_DynamicSql] Script Date: 3/21/2019 11:45:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OrdersSearch_DynamicSql]
@OrderId int = NULL,
@CustomerID int = NULL,
@OrderDateFrom DATE = NULL,
@OrderDateTo DATE = NULL,
@SalespersonPersonID INT = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max),
@params nvarchar(max);
SET @params = N'
@OrderId int,
@CustomerID int,
@OrderDateFrom DATE,
@OrderDateTo DATE,
@SalespersonPersonID INT';
SET @sql = 'SELECT OrderId, OrderDate, CustomerID, SalespersonPersonID
FROM Sales.Orders
WHERE 1=1';
IF @OrderId IS NOT NULL
SET @sql = @sql + ' AND OrderId = @OrderId'
IF @CustomerID IS NOT NULL
SET @sql = @sql + ' AND CustomerID = @CustomerID';
IF @OrderDateFrom IS NOT NULL
SET @sql = @sql + ' AND OrderDate >= @OrderDateFrom';
IF @OrderDateTo IS NOT NULL
SET @sql = @sql + ' AND OrderDate <= @OrderDateTo';
IF @SalespersonPersonID IS NOT NULL
SET @sql = @sql + ' AND SalespersonPersonID = @SalespersonPersonID'
PRINT @sql;
EXEC sys.sp_executesql @sql, @params,
@OrderId,
@CustomerID,
@OrderDateFrom,
@OrderDateTo,
@SalespersonPersonID;
END