T-SQL: How to use parameters in dynamic SQL?

dynamic-sql, sql, sql-server, t-sql

Solution

You must pass in the parameters to sp_executesql. See MSDN for details.

...
 WHERE 
    CreatedBy = @p
...

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

Problem

I have the following dynamic query which is working fine without the `WHERE` clause, which is expecting `UNIQUEIDENTIFIER`. When I pass it in, I don't get a result. I tried `CAST` and `CONVERT`, but no result. I might be doing it wrong, can anybody help? ``` 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 ```

Original source