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

Tussock

Answer 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

Loupe

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

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

Answer 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

Sottish

Answer 3

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

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

Cosgrave

Answer 4

I'm not sure if your variable is getting populated in string format or binary, but you may need to quote the uniqueidentifier in your where clause. If you just select the uniqueidentifier field, does it come back as string or binary?

Hexateuch

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. – Tussock

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. – Hexateuch