Re: How to eval() a variable to get a column name?
Originally posted by MDesigner
OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:
select * from table1 where @statfield = @value
I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:
select * from table1 where key_id = @value
How can I do this?
One way would be to build dynamic sql and execute it
I copy/pasted the following from SQL Server help
Building Statements at Run Time
DECLARE @SQLString NVARCHAR(500)
/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)
/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM Employees' + CHAR(13)
/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%'''
set @sql = N'select distinct ' + @statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @campaign_id)
exec sp_executesql @sql, N'@stat int out', @stat out
set @rc = @@rowcount
@stat shows up as NULL for some reason. did I do something wrong here?