Stored Procedure - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-02 : 05:16:07

I have the following vbscript code that I need to put into a stored procedure but have no idea how to convert it.

I need to do this so that I can setup an automated job in the SQL Server Agent

#####################################################################

sql= "select distinct con_num from delivery"

oRS.Open sql, dsn, 3, 3

if oRS.recordcount > 0 then

do until oRS.EOF = true
sql = "select d.con_num, d.cust_ref, d.sign, l.location as 'mydepot', d.con_date, d.summ_desc, s.stat_desc " &_
"from delivery d " &_
"join location l on l.dep_code = d.dest_dep " &_
"left join status s on s.stat_code = d.stat_code " &_
"where con_num = '" & trim(oRS("con_num")) & "' " &_
"order by con_num, gmt_eve_date desc, gmt_eve_time desc"

set oRS1 = conn.Execute(sql)

first = 1
summarydescription = ""
do until oRS1.EOF = true

if first = 1 then
tempsumm = trim(oRS1("summ_desc"))
tempcon = trim(oRS1("con_num"))
tempref = trim(oRS1("cust_ref"))
tempdate = trim(oRS1("con_date"))
tempdep = trim(oRS1("mydepot"))
first = 0
end if

if trim(oRS1("sign")) <> "" then
summarydescription = "DELIVERED"
end if

if trim(oRS1("summ_desc")) = "" then
if summarydescription = "" then
summarydescription = "IN TRANSIT"
end if
end if
oRS1.Movenext
loop

if summarydescription = "" then
summarydescription = tempsumm
end if

sql = "select con_num from summary where con_num = '" & tempcon & "'"

Set oRS2 = CreateObject("ADODB.Recordset")
oRS2.open sql, conn, 3, 3

if oRS2.recordcount > 0 then
sql = "update summary set summ_desc = '" & summarydescription & "' where con_num = '" & tempcon & "'"
else
sql = "insert into summary (con_num, cust_ref, dest_dep, con_date, summ_desc) " &_
" values ('" & tempcon & "','" & tempref & "','" & tempdep & "','" & tempdate & "','" & summarydescription & "')"
end if
set oRS3 = conn.Execute(sql)

oRS.Movenext

oRS2.Close
Set oRS2 = Nothing

loop

oRS.Close
Set oRS = Nothing
oRS1.Close
Set oRS1 = Nothing

#####################################################################

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-02 : 12:56:40

You could maybe include it as an activex task in a dts package and schedule that.
Would be better as a stored proc probably but as it's already vbscript you might want to try that in the interim.

I started rewriting this as a SP then realised that even as vbscript this is a pretty poor way of doing what it is and makes me doubt that it is doing what is meant.
Try posting the requirements - it looks like it is doing something pretty simple which should take a few lines of code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-03 : 07:37:45

The purpose of the script is to summarise delivery status

I get 10 lines etc for each consignment but the data isn't always accurate. what I need to do is for each distinct consignment is to firstly get the consignment numbers

Secondly, I need to check the correct status. From experience it isn't a case of assuming the latest entry is the correct one so I have areas of logic to check against

1) If field signatory is populated then "DELIVERED"
2) If a problem but field signatory is not full then "EXCEPTION"
3) Else it is a "IN TRANSIT"

When the delivered state has been detected it need not be checked again from the original consignment numbers next time the script is called

If you could get all that into a couple of lines I would be very interested to see it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources