How to eval() a variable to get a column name?

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?

Thanks!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?

Thanks!

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%'''

EXEC sp_executesql @.SQLString|||One problem:

my sql statement is:

select distinct @.stat = packing_shipping from cp_elements where campaign_id = 10

however, if I use execlsql to execute that, @.stat is in some kind of local scope...and is asking to be declared, even though it already is.

How do I get my @.stat return value?? I can't do

select @.stat = exec sp_executesql @.sql

nor this:

exec @.stat = sp_executesql @.sql

help!|||declare @.stat <data type>
exec sp_executesql @.sql, N'@.stat <data type> out', @.stat out

print @.stat|||Hm, that didn't work for some reason..

declare @.stat int

....

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
select @.stat

@.stat shows up as NULL for some reason. did I do something wrong here?|||nevermind. altered the SQL and it worked:

set @.sql = N'select distinct @.stat = ' + @.statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @.campaign_id)