临时表动态添加列

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter PROCEDURE [dbo].[TestingAvailability]
@HoursStartTime INT,
@HoursEndTime INT
AS
BEGIN

DECLARE @VisitingHoursStartTime AS INT --Cursor Local Variables
DECLARE @VisitingHoursEndTime AS INT

SET @VisitingHoursStartTime = @HoursStartTime -- morning 9 am
SET @VisitingHoursEndTime = @HoursEndTime-- evening 6 pm

CREATE TABLE #Temp(Id NVARCHAR(50)) -- Creating Temp Table

-- Loop to add columns to temp table
WHILE (@VisitingHoursStartTime <= @VisitingHoursEndTime)
BEGIN
DECLARE @DynamicSQL VARCHAR(500)

IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('[dbo].[#Temp]') AND NAME = '@VisitingHoursStartTime' )
BEGIN
SET @DynamicSQL = 'ALTER TABLE #Temp ADD ['+ CAST(@VisitingHoursStartTime AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'
EXECUTE (@DynamicSQL)
END

SET @VisitingHoursStartTime = @VisitingHoursStartTime + 1
IF @VisitingHoursStartTime = @VisitingHoursEndTime + 1
BREAK;
END

SELECT * FROM #Temp

IF EXISTS(SELECT * FROM sysobjects WHERE NAME LIKE '%#Temp%')
DROP TABLE #Temp
END