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
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
Visual Basic.NETMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
HLRosenberger
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
ASKER
thanks