Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 7th, 2008, 04:38 AM
Registered User
 
Join Date: Aug 2008
Location: Navi Mumbai, Maharastra, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dynamically creating columns in temp table

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
  #2 (permalink)  
Old August 11th, 2008, 05:09 AM
Registered User
 
Join Date: Aug 2006
Location: hyderabad, Andhrapradesh, India.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

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

  #3 (permalink)  
Old August 11th, 2008, 07:20 AM
Registered User
 
Join Date: Aug 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #4 (permalink)  
Old August 11th, 2008, 11:36 AM
Registered User
 
Join Date: Aug 2008
Location: Navi Mumbai, Maharastra, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old August 11th, 2008, 07:32 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
  #6 (permalink)  
Old August 12th, 2008, 04:29 AM
Registered User
 
Join Date: Aug 2008
Location: Navi Mumbai, Maharastra, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #7 (permalink)  
Old August 12th, 2008, 07:10 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
  #8 (permalink)  
Old August 13th, 2008, 12:05 AM
Registered User
 
Join Date: Aug 2008
Location: Navi Mumbai, Maharastra, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #9 (permalink)  
Old November 14th, 2016, 01:35 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem creating temp XML SCHEMA COLLECTION bijgupt SQL Server 2005 0 May 3rd, 2007 11:58 PM
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
Creating Columns Dynamically?? cbeasle1 Crystal Reports 2 June 10th, 2005 09:15 AM
global temp table vs.permanent table use sofya SQL Server 2000 0 December 17th, 2004 01:57 PM
Creating Table dynamically in Word Document ajish_jose Excel VBA 0 September 30th, 2003 11:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.