Link to home
Start Free TrialLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

Stored Proc; Variable number of columns in temp table.

I have this temp table and code.  Instead of hard coding the column names, Is there a way to DECLARE the number of columns in the temp table, create it accordingly, and then have the UPDATE statement use the dynamically create column names?  

So, currently, the max number of beds is 6.  What I'd really like to be able to do is pass a INT to the proc that indicates the number of beds.  Each bed it it's own record in a real table, and what my proc does is "rotate" the data; it takes all the bed records and create a single record.

CREATE TABLE #tmpTable
(
   Bed1Number bigint,
   Bed1Status varchar(20),
   Bed2Number bigint,
   Bed2Status varchar(20),
   Bed3Number bigint,
   Bed3Status varchar(20),
   Bed4Number bigint,
   Bed4Status varchar(20),
   Bed5Number bigint,
   Bed5Status varchar(20),
   Bed6Number bigint,
   Bed6Status varchar(20)
)

INSERT INTO #tmpTable values(null, null, null, null, null, null, null, null, null, null, null, null) ;



        IF      @BEDNUMBER = 1 UPDATE #tmpTable SET Bed1Number = 1, Bed1Status = @STATUS
      IF      @BEDNUMBER = 2 UPDATE #tmpTable SET Bed2Number = 2, Bed2Status = @STATUS
      IF      @BEDNUMBER = 3 UPDATE #tmpTable SET Bed3Number = 3, Bed3Status = @STATUS
      IF      @BEDNUMBER = 4 UPDATE #tmpTable SET Bed4Number = 4, Bed4Status = @STATUS
      IF      @BEDNUMBER = 5 UPDATE #tmpTable SET Bed5Number = 5, Bed5Status = @STATUS
      IF      @BEDNUMBER = 6 UPDATE #tmpTable SET Bed6Number = 6, Bed6Status = @STATUS
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HLRosenberger

ASKER

I want to plan for more than 6.  So,  dynamic SQL is the way to go?
Yes; AFAIK you don't have a choice.
Instead of putting beds in columns why not put them in rows? It would be more logical, I think. Example:
CREATE TABLE #Bed
 (
    BedNumber bigint,
    BedStatus varchar(20),
 )

INSERT INTO #Bed values(@BEDNUMBER, @STATUS)

Open in new window

thanks