Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #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
Reply With Quote
  #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.

Reply With Quote
  #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


Reply With Quote
  #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
Reply With Quote
  #5 (permalink)  
Old August 11th, 2008, 07:32 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
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
Reply With Quote
  #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
Reply With Quote
  #7 (permalink)  
Old August 12th, 2008, 07:10 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
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
Reply With Quote
  #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
Reply With Quote
  #9 (permalink)  
Old November 14th, 2016, 12: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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 01: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 12:57 PM
Creating Table dynamically in Word Document ajish_jose Excel VBA 0 September 30th, 2003 11:11 PM



All times are GMT -4. The time now is 12:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.