Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks.
A simple example of a stored procedure with dynamic SQL is:
use AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724
Dynamic
SQL allows stored procedures to “write” or dynamically generate their
SQL statements. The most common use case for dynamic SQL is stored
procedures with optional parameters in the WHERE clause. These are
typically called from reports or screens that have multiple, optional
search criteria. This article describes how to write these types of
stored procedures so they execute well and resist SQL injection attacks.
A simple example of a stored procedure with dynamic SQL is:
use AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724
- See more at: http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf
Dynamic
SQL allows stored procedures to “write” or dynamically generate their
SQL statements. The most common use case for dynamic SQL is stored
procedures with optional parameters in the WHERE clause. These are
typically called from reports or screens that have multiple, optional
search criteria. This article describes how to write these types of
stored procedures so they execute well and resist SQL injection attacks.
A simple example of a stored procedure with dynamic SQL is:
use AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724
- See more at: http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf
use
AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724 - See more
at:
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf
use
AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724 - See more
at:
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf
use
AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO
CREATE PROCEDURE [Sales].[GetSalesOrders] (
@CustomerID INT = NULL,
@ContactID INT = NULL,
@debug bit = 0 )
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '
@CustomerParameter INT,
@ContactParameter INT
';
SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';
IF @CustomerID IS NOT NULL
SELECT @SQL = @SQL + N'
AND CustomerID = @CustomerParameter ';
IF @ContactID IS NOT NULL
SELECT @SQL = @SQL + N'
AND ContactID = @ContactParameter ';
IF @debug = 1
PRINT @SQL
EXEC sp_executeSQL
@SQL,
@ParameterDefinition,
@CustomerParameter = @CustomerID,
@ContactParameter = @ContactID;
GO
EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724 - See more
at:
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf
