I want to add column to #temp table dynamically. means through loop i want to add column names.
Example:
StartTime = 9 am
EndTime = 5 pm

I want to generete the time with 30min gap and add that column to #temp table
like this 09:00am, 09:30am, 10:00 am ..... 04:30 pm, 05:00 pm
----------------
I have created Stored Procedure like this but its giving error

        DECLARE @VisitingHoursStartTime AS INT
    DECLARE @VisitingHoursEndTime AS INT

    SET @VisitingHoursStartTime = 9 -- morning 9 am
    SET @VisitingHoursEndTime = 17 -- evening 5 pm


    IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND TYPE IN (N'U'))
        DROP TABLE #temp
    ELSE
        CREATE TABLE #temp(ProviderNAME NVARCHAR(100), VisitTypes NVARCHAR(100),) -- Creating Temp Table

    -- Loop to add columns to temp table
    WHILE (@VisitingHoursStartTime < @VisitingHoursEndTime)
    BEGIN

       IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('#temp') AND NAME = @VisitingHoursStartTime )
    ALTER TABLE #temp ADD [@VisitingHoursStartTime] NVARCHAR(100) NULL

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

    SELECT * FROM #temp

    DROP TABLE #temp

Giribabu

Hi,

which type of error you are getting?
mention error message, we can help you.

1. You are trying to drop a table that does not exist
2. # tables are stored in TempDb so you need to check tempDB.sys.objects
3. Alter Table does not allow Variables for column names

If we knew why you were trying to do this we may be able to suggest a better approach

I solved the error, below is the code
-------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[TestingAvailability]

AS
BEGIN

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

    SET @VisitingHoursStartTime = '09:00' -- morning 9 am
    SET @VisitingHoursEndTime = '17:00' -- 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 ['+ CONVERT(VARCHAR(5),@VisitingHoursStartTime,108) +'] NVARCHAR(100) NULL'
            EXECUTE (@DynamicSQL)
        END

        SET @VisitingHoursStartTime = DateADD(MI,15,@VisitingHoursStartTime)
        IF @VisitingHoursStartTime = @VisitingHoursEndTime
            BREAK;
    END

    SELECT * FROM #Temp

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

-- EXEC TestingAvailability

Giribabu

You do NOT need to drop Temp Tables... they are scope sensitive to the proc that created them and will drop on their own. And, thanks for posting your solution... great idea.

--Jeff Moden

Thanks Jeff Moden for your valuable suggestion, right now i am facing another problem, I have a user table in that i have defined visitStartTime and VisitEndTime, each user will have their own visit timing like 1st user can come at 10am and go at 5pm, 2nd user can come at 9:30 and go at 2pm, those visit timing is divided into 15min each some may can book the timing to visit that user or he can be free if no schedule is made for that time. example if 1st user scheduled 10am to 11:30 and 2 to 5:30 then rest of the timing he is free, here i want to generate free schedule time? how can i achieve that to find the free timing. and want to fill the temp table based on the time,like this
1st user --> 9:30 --> booked
1st user --> 9:45 --> booked
1st user --> 10:00--> booked
1st user --> 10:15--> available
1st user --> 11:30--> booked
2nd user --> 10:00--> booked
2nd user --> 10:15--> booked
2nd user --> 10:30--> available

like this i want to gerate how can i do that


etc.,

Giribabu

As you said... "example if 1st user scheduled 10am to 11:30 and 2 to 5:30 then rest of the timing he is free"....

So why is 10:15AM "available" in the example above?

--Jeff Moden

Hi Jeff, There will be 4 slots for each hour which is divided into 15min.
example 9:00 am to 10:00am will be divided like this
9:00 to 9:15
9:15 to 9:30
9:30 to 9:45
9:45 to 10:00

if i want to book the schedule for 30min between these slot 9:00 to 10:00 then i can book 9:00- 9:30 or 9:15 - 9:45 or 9:30 - 10:00, so 2 slots is booked. if another person want to book after 10:00 then he can book the schedule for particular time. so 9:30 - 10:00 is available he is free that time
9:30 - 9:45 "Available"
9:45 - 10:00 "Available"

Giribabu

hi girish
can you please tell me how to insert data from another table in dynamically column created by you