An exciting adventure with dynamic SQL

dynamic.sql

SELECT * FROM Sales.SalesOrderHeader AS soh



CREATE INDEX jp_filters_not_offline 
    ON Sales.SalesOrderHeader (OrderDate) 
        INCLUDE (SalesOrderID)
        WHERE OnlineOrderFlag <> 0
GO

CREATE PROCEDURE jp_stupid
(
    @OrderDate DATETIME
)
AS
BEGIN

    -- AWWW, no filter
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader AS soh
    WHERE OnlineOrderFlag <> 0 AND OrderDate > @OrderDate
END
GO

ALTER PROCEDURE jp_horrors
(
    @OrderDate DATETIME
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag <> 0 AND OrderDate > ''' + CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    EXEC sp_executesql @sql;
END
GO


SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag <> 0 AND OrderDate > '2007-12-12';

DECLARE @order_date DATETIME = '2007-12-12';

EXEC jp_stupid @order_date;
EXEC jp_horrors @order_date;


-- what if we make this worse?
CREATE PROCEDURE jp_double_stupid
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader AS soh
    WHERE OnlineOrderFlag <> @OnlineFlag AND OrderDate > @OrderDate
END
GO

EXEC jp_double_stupid '2007-12-12', 0




-- Let's fix that:
ALTER PROCEDURE jp_fixing_the_stupid
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag <> ' + CAST(@OnlineFlag AS NVARCHAR(1));

    SET @sql += ' AND OrderDate > ''' + CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    PRINT @sql

    EXEC sp_executesql @sql;
END

EXEC jp_fixing_the_stupid '2007-12-12', 0