Column Query with a Variable

I have 30 fields in a table called ADP_Pay_Detail that I need to query with
the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
DED_CD_30. I have coded a counter with a WHILE statement to increment the
last number of this string, and assign the entire string to a varchar
variable named @.column_amt. All of the type conversions I need to do are
correct. I then try to write a query using this variable as a field name.
An example would be
SELECT *
FROM ADP_Pay_Detail
where @.column_amt = 30
This isn't working at all. Can anyone give me a suggestion about how to
accomplish what I want to do? The query I have to write is much more
complicated than my example, and I'd hate to have to write it 30 times to
query each column!
Thanks!
Keith
Keith>I have 30 fields in a table called ADP_Pay_Detail that I need to query with
> the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
> DED_CD_30.
Just curious if you have considered a more sensible and relational design?
Are you going to add DED_CD_31 -> DED_CD_n at some point?

> SELECT *
> FROM ADP_Pay_Detail
> where @.column_amt = 30
> This isn't working at all.
That is right, because T-SQL is not a language that allows you to construct
queries like this, you must tell it about the metadata and not let it decide
on its own. My first suggestion would be to pass all 30 columns back, and
let the app display only the column(s) it needs. This will be more flexible
as you can later decide to show 2 or 3 or all columns and you won't have
much change to do...
My more intuitive response would be to store the data and its value as data,
instead of mixing data and metadata. An example might be:
CREATE TABLE dbo.ADP_Pay_Detail
(
pdID INT PRIMARY KEY
-- , other columns
)
CREATE TABLE dbo.ADP_Pay_Detail_DED_CD
(
pdID INT FOREIGN KEY REFERENCES dbo.ADP_Pay_Detail(pdID),
column_amt TINYINT, -- CHECK CONSTRAINT perhaps?
value INT
)
Now you can say
SELECT * FROM ADP_Pay_Detail d
INNER JOIN ADP_Pay_Detail_DED_CD c
ON d.pdID = c.pdID
WHERE c.column_amt = 30
And you don't have any silly column names with data embedded in them. (Next
you can work on manageable table names.)

> Can anyone give me a suggestion about how to
> accomplish what I want to do?
As a last resort, dynamic SQL. PLEASE READ THE FOLLOWING and heed the
warnings seriously:
http://www.sommarskog.se/dynamic_sql.html|||>> I have 30 fields [sic] in a table called ADP_Pay_Detail that I need to query w
ith
the exact same query. Each field [sic] is named DED_CD_1, DED_CD_2,
etc. up to DED_CD_30. <<
Rows are not records; fields are not columns; tables are not files.
You need to get a book on RDBMS basics and read the chapter on Normal
Forms. What you have here is a 1950's COBOL file layout with a fake
OCCURS clause.
Apparently SQL is the first compiled language you have ever used. It
is also a declarative language, so writing loops in a proprietary 3GL
is a sign of poor coding.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.