How do I pass a column as a parameter in Stored Procedure?

Question

How do I pass and use the column name to retrieve a bigint variable in the actual column?

DECLARE @personID BIGINT,
DECLARE @queryString varchar(500)

Set @queryString = 'Select @personID = ' + @PersonColumnID + ' from dbo.Loss_Witness where WitnessID = @witnessID'
exec(@queryString)

Error message states "Must declare variable '@personID'." I also tried

Set @queryString = 'Select ' + @personID + ' = ' + @witnessPersonID + ' from dbo.Loss_Witness where WitnessID = @witnessID'

And got the error message "Error converting data type varchar to bigint."

Any thoughts?

Answer 1

You need to specify that @personID is an out parameter with the OUTPUT keyword:

DECLARE @SQL NVARCHAR(max)
    SET @SQL = 'SELECT @personID = w.' + @PersonColumnID + ' 
                  FROM dbo.Loss_Witness w
                 WHERE w.witnessID = @witnessID '

BEGIN 

  EXEC sp_executesql @SQL, @personID = @personID OUTPUT, @witnessID

END

There's another example here.

Answer 2

Also note that there is potentially an SQL Injection security hole in your code. If you're not sanitizing @PersonColumnID then you could be in big trouble.