Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: 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 Hanzelman

  2. #2
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61
    This might work..

    alter procedure u_sp_x
    @semester int
    as
    select * from semester
    where @semester = 'semester 1'

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    6
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett proposing dynamic SQL?!

    What's the weather forecast in Hell, today?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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!)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •