Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Liverpool
    Posts
    2

    Unanswered: Using a stored procedure parameter to access a column

    I trying to create a general stored procedure which updates 1 out of 140 columns depending on the column name provided as a parameter.
    I'm not having much luck, just wondering if anyone else had tried to do this and whether it is actually possible?
    Any help would be much appreciated

    Chris

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, it is possible. No, it is not for the faint of heart.

    I have to question whether the design makes sense. In a situation like that, it rarely does make sense when you step back and think about it.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Location
    Liverpool
    Posts
    2
    Basically its a table of 'performances' for a small venue, and there are a number of columns representing the status of the seats for that date (available/booked).

    The stored procedure is designed to 'book' a requested seat, the input parameter is the required seat eg. 'A1'. The easiest way I could see to do this was to then use this seat name to update the appropriate column.
    A unique performanceID is also passed as a parameter so it would only ever access a single record....

    something like:

    @RequireSeat
    @PerformanceID

    UPDATE Performances
    SET (@RequiredSeat) = 1 --represents 'booked'
    WHERE Performances.PerformanceID = @PerformancesID


    I wanted to get the 'SET' statement to use the value of the @RequiredSeat parameter as the column name...

    The reason I'm trying to do it like this, is I couldn't come up with a better way of identifying which column to update without having a seperate stored procedure for each one, which seemed inefficient...

    Any suggestions would be much appreciated
    Regards
    Chris

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is in your database design. It is not normalized. You should not be storing seats as additional columns in the table, but in a separate table where each seat is a separate record.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about changing your design to something like:
    PHP Code:
    CREATE TABLE tEvents (
       
    eventID              INT             IDENTITY
       PRIMARY KEY 
    (eventID)
    ,  [
    Date]               DATETIME        NOT NULL
    ,  [description]        VARCHAR(50)     NOT NULL
       
    )

    CREATE TABLE tPatrons (
       
    patronID             INT             IDENTITY
       PRIMARY KEY 
    (patronID)
    ,  [
    name]               VARCHAR(50)     NOT NULL
       
    )

    CREATE TABLE tSeats (
       
    seatID               INT        IDENTITY
       PRIMARY KEY 
    (seatId)
    ,  [
    description]        VARCHAR(50)    NOT NULL
       
    )

    CREATE TABLE tBookings (
       
    eventID              INT             NOT NULL
    ,  patronID             INT             NOT NULL
    ,  seatID               INT             NOT NULL
       PRIMARY KEY 
    (eventIDpatronIDseatID)
       ) 
    This allows you a LOT more flexibility, at least in my opinion.

    -PatP

Posting Permissions

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