views:

1041

answers:

3

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
A: 

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?

Lazy Bob
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.
Yaser Ahmed
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.
Lazy Bob
+1  A: 
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = '@p_CreatedBy UNIQUEIDENTIFIER'

EXEC sp_executesql @sql, @ParmDefinition, @p_CreatedBy = @p_CreatedBy
Greco
i tried this, it is not working giving me the followig error"Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'."
Yaser Ahmed
thanks it worked for me
Yaser Ahmed
+2  A: 

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
Tomalak
i tried this, it is not working giving me the followig error "Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'."
Yaser Ahmed
Make the string NCHAR by adding an 'N': EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', ...
Remus Rusanu
Oops, I forgot that dreaded N. Thanks.
Tomalak