How do I pass a column as a parameter in Stored Procedure?
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?
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.
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.
精彩评论