How to Correctly Pass Parameters in T-SQL Dynamic SQL Statements?

Passing Parameters in Dynamic SQL Statements with T-SQL

In T-SQL, dynamic SQL provides a way to construct and execute SQL statements programmatically. When using parameters in dynamic SQL, it's important to understand the correct syntax.

Consider the following example:

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

When executing this procedure without a WHERE clause, it works fine. However, when a WHERE clause with a UNIQUEIDENTIFIER parameter is added, no results are returned. The issue lies in the incorrect usage of parameters in the dynamic SQL statement.

To resolve this issue, parameters must be passed to the sp_executesql statement. As per MSDN:

...
 WHERE 
    CreatedBy = @p
...

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

In this modified code, the parameter @p is used in the WHERE clause, and the sp_executesql statement is invoked with the @p parameter being set to the value of @p_CreatedBY. This ensures that the UNIQUEIDENTIFIER parameter is correctly passed to the dynamic SQL statement.

The above is the detailed content of How to Correctly Pass Parameters in T-SQL Dynamic SQL Statements?. For more information, please follow other related articles on the PHP Chinese website!