dynamic column name (using where)

Hello All,

I'm trying to us a parameter as column name like:

Select * From SM_Clients
where @.Selection like @.SelectionChar+'%'

This dos not work.

does someone have a solution

tks stroek

You cannot do that with the 'normal' way of executing T-SQL.

You can do it with dynamic SQL. You must first build the query as a string, and then execute the string. Somewhat like this:

DECLARE @.SQLString nvarchar(1000)

SET @.SQLString = 'SELECT * FROM SM_Clients WHERE ' +

@.Selection +

' LIKE ' @.SelectionChar + '%;'

Then just to verify that you have done the above correctly, you could test like this in a Query Window:

PRINT @.SQLString

Once you are satisifed that the query is properly constructed, you would execute it like this:

EXECUTE sp_executesql @.SQLString

However, if there is any chance that a user will be typing in values for @.Selection OR @.SelectionChar, your server 'could' be wide open for what is known as a SQL INJECTION ATTACK.

I highly recommend reviewing these articles:

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||OK
tks I will try it.|||How can I use this in a stored procedure?|||

hi,

http://www.sommarskog.se/dynamic_sql.html

regards