Passing parameters into sp_executesql

Question

I have this simple dynamic SQL code:

declare @cmd nvarchar(100)
declare @paramDef nvarchar(100) = N'@p nvarchar(20)'
declare @p nvarchar(10) = N'SubTotal'
set @cmd = N'select @p from Sales.SalesOrderHeader'
exec sp_executesql @cmd, @paramDef, @p

However, running this outputs column of repeated word "SubTotal" instead of actual values of SubTotal column. Am I not passing the parameters correctly?

Answer 1

You can't use parameters for table or column names. You have to modify the actual SQL:

declare @cmd nvarchar(100)
declare @p nvarchar(10) = N'SubTotal'
set @cmd = N'select ' + QUOTENAME(@p) + N' from Sales.SalesOrderHeader'
exec sp_executesql @cmd

It's perfectly valid to include constant values in a select statement, and that's similar to what your attempt is doing (just using a parameter rather than a literal value). It wouldn't be (too) surprising to you, hopefully, that a query like:

select 'abc' from sys.objects

would just return the string abc multiple times - and that's what's happening when you use your parameter as well.