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

August 7th, 2008, 04:38 AM
|
Registered User
|
|
Join Date: Aug 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

August 11th, 2008, 05:09 AM
|
Registered User
|
|
Join Date: Aug 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi,
which type of error you are getting?
mention error message, we can help you.
|

August 11th, 2008, 07:20 AM
|
Registered User
|
|
Join Date: Aug 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

August 11th, 2008, 11:36 AM
|
Registered User
|
|
Join Date: Aug 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

August 11th, 2008, 07:32 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|

August 12th, 2008, 04:29 AM
|
Registered User
|
|
Join Date: Aug 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

August 12th, 2008, 07:10 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|

August 13th, 2008, 12:05 AM
|
Registered User
|
|
Join Date: Aug 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 14th, 2016, 01:35 AM
|
Registered User
|
|
Join Date: Nov 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
hi girish
can you please tell me how to insert data from another table in dynamically column created by you
|
|
 |