开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜