How do I query a value dynamically in T-SQL?
For whatever reason, I can't seem to get a value out dynamically from SQL.
declare @SQL nvarchar(max)
declare @FieldNa开发者_开发技巧me nvarchar(255)
declare @FieldValue nvarchar(max)
select @SQL = 'SELECT TOP 1 ' + @fieldname
+' FROM MyTable WHERE CM_CASE_YEAR = ' + LEFT(@ClaimNumber, 2)
+' AND CM_CASE_NUMBER = ' + RIGHT(@ClaimNumber, 6)
exec sp_executesql @sql, @FieldValue OUTPUT
select @FieldName + ' - ' + @FieldValue
When I run the @SQL
query in another window, it displays one column with one value.
But, unfortunately when I try this, @FieldValue always comes back NULL.
Did I miss something the day they taught sp_executesql? Obviously! But what?
See this example
DECLARE @SQL NVARCHAR(MAX)
DECLARE @FieldName sysname = 'name'
DECLARE @FieldValue NVARCHAR(MAX)
SELECT @SQL = 'SELECT TOP 1
@FieldValue =' + QUOTENAME(@FieldName) + ' FROM sys.objects'
EXEC sp_executesql @SQL,
N'@FieldValue nvarchar(max) OUTPUT',
@FieldValue =@FieldValue OUTPUT
SELECT @FieldName + ' - ' + @FieldValue
sp_executesql
returns (generates) a resultset. @FieldValue
is meaningless in the code above - sp_executesql
won't put any value into that variable.
精彩评论