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

qujck · Accepted Answer · 2018-04-03 12:40:11Z

30

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

edited Apr 3, 2018 at 12:40

qujck

14.6k4 gold badges48 silver badges77 bronze badges

answered Jun 24, 2009 at 7:15

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yaser Ahmed Over a year ago

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

Remus Rusanu Over a year ago

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

OJisBad · Accepted Answer · 2018-10-15 13:49:20Z

12

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

answered Oct 15, 2018 at 13:49

OJisBad

4175 silver badges10 bronze badges

Comments

Dimi Takis · Accepted Answer · 2009-06-24 07:13:46Z

4

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

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

answered Jun 24, 2009 at 7:13

Dimi Takis

4,9563 gold badges33 silver badges44 bronze badges

Comments

Lazy Bob · Accepted Answer · 2009-06-24 07:02:43Z

0

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?

answered Jun 24, 2009 at 7:02

Lazy Bob

4493 silver badges9 bronze badges

2 Comments

Yaser Ahmed Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related