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 stroekDynamic SQL:
http://www.sommarskog.se/dynamic_sql.html|||I wanted a read but that website is blocked :mad:

Your request was denied because of its content categorization: "Personal Web Sites;Society and Lifestyles"|||yikes, george, yikes

then again, you do have a tendency to mess about when you should be working, and if others in your company are the same, then no wonder they've put controls on you...

:)|||then again, you do have a tendency to mess about when you should be working

:shocked: How very dare you!|||Sorry but I didn't find any simple solution for my problem.
Is there no one that can give me a simple solution?

Gr stroek|||DECLARE @.s varchar(150)

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

EXEC (@.s)

?|||Yeah, my work wouldn't consider time spent here to be "messing about";)|||Mine either - an invaluable learning resource.
You only get out what you put in ;)|||i would've said skiving, but the rest of you lot would've thought i'd gone barmy|||I'm ahead of schedule on my projects and I consider this a constructive use of my time :)
I think I've learnt more from these forums than I have from all the courses I have been on combined.
Furthermore; learning by doing is also a much more effective way of learning imo.|||Unfortunately, I was being sarcastic. Spending a few hours of my work day giving support to others outside of the company (as I've done here in the past) would be severely frowned upon.

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