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

Question

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

Solution 1

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

Solution 2

Multiple parameter syntax. Maybe this will save someone an extra Google Search:

exec sp_executesql 
    @qry, 
    N'@value1 int, @value2 int, @currentValue int', 
    @value1 = @value1, @value2 = @value2, @currentValue = @currentValue

Solution 3

DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = '@p_CreatedBy UNIQUEIDENTIFIER'

EXEC sp_executesql @sql, @ParmDefinition, @p_CreatedBy = @p_CreatedBy

Comments

[Yaser Ahmed]

i even tried passing the exact uniqueidentifier with quotes, but it is giving incorrect syntax when im doing so. any example how to do this.

[Lazy Bob]

wait a minute... don't you need to put the var name outside the quotes? so along the lines of WHERE CreatedBy = ' + @p_CreatedBy + ' ) I think that way actually puts the literal string into the sql.

[Yaser Ahmed]

i tried this, it is not working giving me the followig error "Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'."

[Remus Rusanu]

Make the string NCHAR by adding an 'N': EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', ...