how to solve parameter in sp_executesql

Question

Hi,

I have the following query:

create proc [dbo].GetCustById
as
DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select @columnName from customer where custId = @custId'
SET @Param = N'@columnName varchar(10), @custId int'

EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42'

But it always return a string "Address1" instead of the value of Address1 column. Anyone can help?

thanks

Answer 1

A parameter is immediately escaped based on the data type--that's why you're getting the value "Address1" returned rather than the actual value for the column.

Submit the column name not as a parameter, but as a concatenated string:

DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select '+ @columnName +' from customer where custId = @custId'
SET @Param = N'@custId int'

EXEC sp_executesql @sql, @Param , @custId = 42

Read more about the behavior here.

The only other alternative I'm aware of requires that you use decision logic to redirect to a query where the column name is statically defined:

IF @columname = 'Address1' 
BEGIN

  SET @sql = 'select Address1 from customer where custId = @custId'

END