Insert data into table trough proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert data into table trough proc

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2011-11-17 : 06:30:24

I have blow mention table and i had to insert the data throught procedure

CREATE TABLE [dbo].[tSchedule](
[Tid] [char](5) NULL,
[SlotDate] [date] NULL,
[SlotFromHrs] [char](5) NULL,
[SlotToHrs] [char](5) NULL,
[IsBooked] [bit] NULL)

user input -

Tid - 'T01'
SlotDate - '17 Nov 2011'
Slot Form - 09:30 hrs
Slot To - 18:00 hrs
Slot time - 10:00 min

data insert should be like-
'T01' '17 Nov 2011' '09:30','09:40'
'T01' '17 Nov 2011' '09:40','09:50'
'T01' '17 Nov 2011' '09:50','10:00'
'T01' '17 Nov 2011' '10:00','10:00'
...continue till 18:00 hrs

Please guide

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-17 : 09:40:37

why are you using CHAR instead of DATE? It boggles the mind...
You could then use DATEDIFF to figure out your times.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

asm
Posting Yak Master

140 Posts

Posted - 2011-11-17 : 10:19:00

Ok, If datetime it taken then it is possible...
I will try it...

asm
Posting Yak Master

140 Posts

Posted - 2011-11-17 : 11:10:58

Hi, I change the table accordingly... I am able to find the total min. now my problem how to insert the divided time slot through procedure...

new table structure
CREATE TABLE [dbo].[TSchedule]([DoctorId] [char](5) NULL,[SlotDate] [smalldatetime] NULL,[SlotFromHrs] [smalldatetime] NULL,[SlotToHrs] [smalldatetime] NULL)

proc--
Create Procedure spDrSchedule
@DRID char(5),
@ScDate smalldatetime,
@ScFromHrs smalldatetime,
@ScToHrs smalldatetime,
@ScTimeMin smallint
AS
Declare
@TotalSlot smallint,
@NewSlotFrom smalldatetime,
@NewSlotTo smalldatetime

Begin
Set @TotalSlot = (DATEDIFF(MINUTE ,@ScFromHrs,@ScToHrs)) / @ScTimeMin
Declare tot_Slot Cursor For
Select @TotalSlot
Open tot_Slot
Fetch Next From tot_Slot
While @@FETCH_STATUS = 0
BEGIN
Print '1'
Insert into DrSchedule (DoctorId,SlotDate,SlotFromHrs,SlotToHrs) Values(@DRID,@ScDate,@NewSlotFrom,@NewSlotTo)
FETCH NEXT FROM tot_Slot;
END
Close tot_Slot
Deallocate tot_Slot
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:55:02

[code]
Create Procedure spDrSchedule
@DRID char(5),
@ScDate smalldatetime,
@ScFromHrs smalldatetime,
@ScToHrs smalldatetime,
@ScTimeMin smallint
AS

;With Time_Slots
AS
(
SELECT DATEADD(ss,DATEDIFF(ss,0,@ScFromHrs),@ScDate ) AS Start,DATEADD(ss,DATEDIFF(ss,0,@ScFromHrs)+ (@ScTimeMin * 60),@ScDate ) AS [End]
UNION ALL
SELECT [End],DATEADD(mi,@ScTimeMin,[End])
FROM Time_Slots
WHERE DATEADD(mi,@ScTimeMin,[End])<= DATEADD(ss,DATEDIFF(ss,0,REPLACE(@ScToHrs,' hrs','')),@ScDate )
)

INSERT [dbo].[TSchedule]
SELECT @DRID,@ScDate,* FROM Time_Slots
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asm
Posting Yak Master

140 Posts

Posted - 2011-11-17 : 12:15:43

I tried to execute this proc but some error through...

Execute spDrSchedule 'DR005','2011-11-17 09:00:00', '2011-11-17 09:30:00', '2011-11-17 18:00:00', 10

Msg 535, Level 16, State 0, Procedure spDrSchedule, Line 8
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 12:36:31

just call it like


Execute spDrSchedule 'DR005','2011-11-17', '09:30:00', '18:00:00', 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asm
Posting Yak Master

140 Posts

Posted - 2011-11-17 : 22:14:53

thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 01:51:57

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 02:11:55

so sad

EXEC spDrSchedule
@DRID =
,@ScDate =
,@ScFromHrs =
,@ScToHrs=
,@ScTimeMin=

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources