Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)