I'm trying to write some code that will choose certain fields out of a custom table based on a declared variable.
My variable is as follows:
DECLARE @cfyear char(2)SET @cfyear = '09'
And my code is supposed to choose the appropriate columns for that financial year, like so:
SELECT c.customer_no, c.lname, s.esal1_desc, y.description as cust_type, 'l.fc1_fy'+@cfyear, l.fc2_fy09, l.fc3_fy09, l.base_gift_09, l.spec_evts_09, l.production_09 FROM...
Now I'm getting an error (Cannot convert a char value to money. The char value has incorrect syntax.) that confounds me. I don't care about the error so much as I need to find a way to use that variable's value to choose the fields with that value at the end of their name (i.e. a user-selected year). Is it possible?
Thanks Tessiturians.
BONUS NON-SYNTAX RELATED TRIVIA: The Praying Mantis is the only insect that can stand on one leg.
The only thing I can think of is to create a second variable that will contain the entire SQL query using dynamic SQL.
You can then execute that variable almost like a stored procedure. I haven't done this in awhile but the essence of it is to concatenate the entire query up front, load it into the variable and then tell SQL to execute the variable. There is a decent (if overly long) HOWTO here http://www.sommarskog.se/dynamic_sql.html
You should also be able to find other examples by googling "Dynamic SQL"