0

Please see the code below:

declare @value as nvarchar(100)
declare @sql as nvarchar(100)
set @Value = ' WHERE ID=@Value'

set @sql='SELECT * FROM person ' + @Value
EXECUTE sp_executesql @sql

The error I get is: Must declare the scalar variable "@Value". Why is this?

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • Neither "duplicate" question is relevant to this one. The first deals with parameterizing things that can't be parameterized (column access and table access). As for the second, variable casing is a red herring here. – Mark Sowul Mar 06 '14 at 18:50

1 Answers1

4

The dynamic SQL is executed separately, so the variables aren't available (set @Value = ' WHERE ID=@Value'), so you need to have sp_executesql declare them for itself.

What you can do is pass them in as follows:

EXECUTE sp_executesql @sql, '@Value nvarchar(100)', @Value = @Value;

See also this question: How to set value to variable using 'execute' in t-sql?

Community
  • 1
  • 1
Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
  • Are you sure about that`? TO my knowledge they are session based. Not having used dynamic SQL for years, but that soudns like a totally ok scenario for me. – TomTom Mar 06 '14 at 14:40
  • Of course, that's the whole purpose for sp_executesql to take in parameter definitions. http://technet.microsoft.com/en-us/library/ms175170.aspx – Mark Sowul Mar 06 '14 at 18:50