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
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You'd need to use dynamic SQL to get a variable number of columns in an UPDATE statement.

But you could just create the temp table with all 6, and set the extra columns to NULL if the value of @BEDNUMBER indicates they are not needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
I want to plan for more than 6.  So,  dynamic SQL is the way to go?
0
Scott PletcherSenior DBACommented:
Yes; AFAIK you don't have a choice.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
HLRosenbergerAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.