CODEWITHSUNDEEP
sqlt-sqldynamicsp-executesql
user724198
user724198

Reputation:

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 @FieldName 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?

Upvotes: 1

Views: 114

Answers (2)

Martin Smith
Martin Smith

Reputation: 453298

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

Upvotes: 1

Will A
Will A

Reputation: 24988

sp_executesql returns (generates) a resultset. @FieldValue is meaningless in the code above - sp_executesql won't put any value into that variable.

Upvotes: 0

Related Questions