need help-how to optimise the code

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

need help-how to optimise the code

Author

Topic

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 01:42:17

Hi all,

Currently i am working with a online shopping project in that i have to create a new table called 'greendelivery slot' table.. In that table i need to enter the data .i have done like dis... in dis i have to enter each every data it will tedious if i want to enter a large no : of data .How we can optimise this code....

''Declare @temp int
Set @temp=1
while @temp<=7
begin
INSERT INTO [dxsh].[dbo].[GreenDeliverySlots]
([PCode]
,[ro_no]
,[svc_no]
,[dow]
,[deflt_slot_start]
,[deflt_slot_end]
,[GreenPoints])
VALUES
('L15 8JS',2613,1,@temp,'1000','1200',20)
if @temp >7
break
else
set @temp=@temp+1
End''

In this i have used a temp variable to enter days in a week
PCode- address
ro_no- store no
svc_no- defaultly 1
dow- date of week
deflt_slot_start-starting a slot time
deflt_slot_end-ending a slot time
GreenPoints-points given for customers

with regards,
Divya

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 01:59:15

can we use a stored procedure for this??????????

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 03:30:20

[code]CREATE PROCEDURE dbo.usp_MyGreen
AS

SET NOCOUNT ON

INSERT dxsh.dbo.GreenDeliverySlots
(
PCode,
ro_no,
svc_no,
dow,
deflt_slot_start,
deflt_slot_end,
GreenPoints
)
SELECT 'L15 8JS',
2613,
1,
@temp,
'1000',
'1200',
20
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 7[/code]

E 12°55'05.63"
N 56°04'39.26"

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 03:35:14

hi peso...
i hav tried dat one but it will come like dis only...
L15 8JS 2613 1 1 1000 1200 20
L15 8JS 2613 1 2 1000 1200 20
L15 8JS 2613 1 3 1000 1200 20
L15 8JS 2613 1 4 1000 1200 20
L15 8JS 2613 1 5 1000 1200 20
L15 8JS 2613 1 6 1000 1200 20
L15 8JS 2613 1 7 1000 1200 20

here also same thing will happen again n again we have to enter pcode,ro_no ,deflt_start,deflt_end,greenpoints

i want to enter for differnt pcode and diferent deflt_start n deflt_end time

do we have any oder easier way for dat

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 03:42:57

[code]CREATE PROCEDURE dbo.usp_MyGreen
(
@Pcode {some datatype here},
@ro_no {some datatype here},
@svc_no {some datatype here},
@deflt_slot_start {some datatype here},
@deflt_slot_end {some datatype here},
@GreenPoints {some datatype here}
)
AS

SET NOCOUNT ON

INSERT dxsh.dbo.GreenDeliverySlots
(
PCode,
ro_no,
svc_no,
dow,
deflt_slot_start,
deflt_slot_end,
GreenPoints
)
SELECT @pcode,
@ro_no,
@svc_no,
Number,
@deflt_slot_start,
@deflt_slot_end,
@GreenPoints
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 7[/code]

E 12°55'05.63"
N 56°04'39.26"

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 04:03:07

hi..
again it will com like dis one i want same pcode ro_no but differnt deflt_start,Deflt_end for diffent dow....

L15 8JS 2613 1 1 1000 1200 20
L15 8JS 2613 1 2 1000 1200 20
L15 8JS 2613 1 3 1000 1200 20
L15 8JS 2613 1 4 1000 1200 20
L15 8JS 2613 1 5 1000 1200 20
L15 8JS 2613 1 6 1000 1200 20
L15 8JS 2613 1 7 1000 1200 20
L15 8JS 2613 1 1 1000 1200 20
L15 8JS 2613 1 2 1000 1200 20
L15 8JS 2613 1 3 1000 1200 20
L15 8JS 2613 1 4 1000 1200 20
L15 8JS 2613 1 5 1000 1200 20
L15 8JS 2613 1 6 1000 1200 20
L15 8JS 2613 1 7 1000 1200 20

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:12:38

You haven't told use what you want the the columns to be calculated with.
Can we make a guess? Or you care to tell us the rules for calculating the two columns?

E 12°55'05.63"
N 56°04'39.26"

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 04:20:31

hi peso...

first of i sorry u... i thought dat by ma 3 rd posting.. i tried to explain....like dis

hi peso...
i hav tried dat one but it will come like dis only...
L15 8JS 2613 1 1 1000 1200 20
L15 8JS 2613 1 2 1000 1200 20
L15 8JS 2613 1 3 1000 1200 20
L15 8JS 2613 1 4 1000 1200 20
L15 8JS 2613 1 5 1000 1200 20
L15 8JS 2613 1 6 1000 1200 20
L15 8JS 2613 1 7 1000 1200 20

here also same thing will happen again n again we have to enter pcode,ro_no ,deflt_start,deflt_end,greenpoints

""i want to enter for differnt pcode and diferent deflt_start n deflt_end time""

do we have any oder easier way for dat

with regards,
Divya

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:36:32

I am sorry, I do not understand you at all.
What is your expected resultset? What do you want in return from the stored procedure?

E 12°55'05.63"
N 56°04'39.26"

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 04:41:35

hi..peso
NO need saying sorry to me... i feel bad if u say sorry.. i knw it 's ma mistak i havn't explained ma reqmnts...
i want to enter deflt_start time deflt _end time for differnt pcode, ro_no
but 7 time pcode n ro_no will repeat but deflt_srt time and deflt_end time n greenpoint has to differnt .. so how we can write a query for dat so we need to enter data for dat

i think u got ma scenario...if need clarifcation jst reply me..

with regards,
Divya

divyagr
Starting Member

29 Posts

Posted - 2009-02-05 : 04:49:41

hi i want it like dis

L15 8JS 2613 1 1 1000 1200 20
L15 8JS 2613 1 2 1100 1300 20
L15 8JS 2613 1 3 1200 1400 20
L15 8JS 2613 1 4 1400 1600 20
L15 8JS 2613 1 5 1600 1800 20
L15 8JS 2613 1 6 1800 2000 20
L15 8JS 2613 1 7 2000 2200 20
CB6 1DJ 2470 1 1 1100 1300 40
CB6 1DJ 2470 1 2 1200 1400 20
CB6 1DJ 2470 1 3 1300 1500 10
CB6 1DJ 2470 1 4 1700 2100 20
CB6 1DJ 2470 1 5 1100 1300 20
CB6 1DJ 2470 1 6 1200 1400 20
CB6 1DJ 2470 1 7 1100 1300 20

pcode n ro_no will same for 7 times but deflt_start n deflt_end n greenponts will change

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:56:23

HOW WILL THEY CHANGE?
What are the business rules for setting the values????

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:58:00

Please read this blog post VERY CAREFULLY!
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources