How to Correctly Pass UNIQUEIDENTIFIER Parameters in Dynamic T-SQL using sp_executesql?

Passing Parameters in Dynamic T-SQL using sp_executesql

In dynamic T-SQL, passing parameters to queries executed using sp_executesql can be a challenge. This article addresses a common issue faced when using dynamic SQL with WHERE clauses expecting UNIQUEIDENTIFIER parameters.

Consider the following example, where the sp_executesql statement is missing the necessary parameter binding:

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 this procedure is executed, the WHERE clause condition will fail because the @p_CreatedBy parameter is not bound to the dynamic SQL query.

To resolve this issue, parameters must be explicitly passed to sp_executesql. According to MSDN, the correct syntax is:

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

This ensures that the @p_CreatedBy parameter is assigned to the @p parameter in the dynamic SQL statement, enabling the WHERE clause to correctly evaluate the condition.

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