How to Properly Parameterize Pass-Through Queries in T-SQL's Dynamic SQL?

Dynamic SQL in T-SQL: Paramaterized Pass-Through

In T-SQL, dynamic SQL can be a useful tool for constructing queries at runtime. However, it is important to understand how to handle parameters within dynamic SQL statements.

Consider the following dynamic query:

CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */
(
  @p_CreatedBy UNIQUEIDENTIFIER
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = '
DECLARE @p_CreatedBY UNIQUEIDENTIFIER

SELECT 
  DateTime,
  Subject,
  CreatedBy
FROM
(
  SELECT 
    DateTime, Subject, CreatedBy, 
    ROW_NUMBER() OVER(ORDER BY DateTime ) AS Indexing
  FROM
    ComposeMail
  WHERE 
    CreatedBy = @p_CreatedBy /* <--- the problem is in this condition */
) AS NewDataTable
';

EXEC sp_executesql @sql

This query attempts to select data from the ComposeMail table based on the @p_CreatedBy parameter. However, when executing this query with parameters, no results are returned.

To resolve this issue, it is crucial to pass the parameters to sp_executesql, as demonstrated in the following code:

...
WHERE 
    CreatedBy = @p
...

EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY

In this updated code, the @p parameter is defined as a UNIQUEIDENTIFIER type and its value is set to the @p_CreatedBY parameter. This ensures that the parameter is properly passed to the dynamic SQL statement.

By following this technique, you can effectively use parameters within dynamic SQL queries in T-SQL.

The above is the detailed content of How to Properly Parameterize Pass-Through Queries in T-SQL's Dynamic SQL?. For more information, please follow other related articles on the PHP Chinese website!