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
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