Need an expert advice

[kebele]

I am using the below sql script in my asp.net page and i am not sure if this is an efficient way? if not what should i do? shall i just create view and read it from there? can someone advise me on this. thanks for your help and time.
here is the script
'sql to generate the planholders list.Don't alter this code
Dim SQL As String = " SELECT DISTINCT Decode(trim(Min(J.ROUTE)),null,'Un known', Min(J.ROUTE)) rt, v.vendor vd, r.addrnum ad, v.vnamel vn, "
SQL = SQL & "TRIM (r.aaddr1 || decode(trim(r.aaddr2),null,'',' - ') || r.aaddr2) ad1,"
SQL = SQL & "p.billto, r.acity cy, r.astate st,r.azipcode zp, "
SQL = SQL & " substr(decode(trim(r.vasst1),null, 'N/A','000/000-0000?','N/A','000/000-0000','N/A', r.vasst1),1,12) fx, "
SQL = SQL & " substr(decode(trim(r.aphone),null, 'N/A','000/000-0000?','N/A', r.aphone),1,12)ph, "
SQL = SQL & " substr(l.letting,3,2)||'-'|| substr(l.letting,5,2)||'-'|| substr(l.letting,1,2)lt, "
SQL = SQL & " l.CALL cl, l.lcontid cid, q.cprojnum sp, q.cfacssup ds,q.ccnty1 cty "
SQL = SQL & "FROM VENDOR V, VENDADDR R, LETPROP L, PLANHOLD P,PROPOSAL Q, PROJECT J,PROPPROJ K "
SQL = SQL & "WHERE V.VENDOR = R.VENDOR "
SQL = SQL & "AND K.CONTID = Q.CONTID "
SQL = SQL & "AND K.PCN = J.PCN "
SQL = SQL & "AND L.LCONTID = K.CONTID "
'value for contract id passed using querystring
SQL = SQL & "and l.lcontid = " & "'" & ContractId & "'"
SQL = SQL & "AND P.VENDOR = V. VENDOR "
SQL = SQL & "AND L.LETTING = P.LETTING "
SQL = SQL & "AND L.CALL = P.CALL "
SQL = SQL & "AND R.ADDRNUM = P.BILLTO "
SQL = SQL & "group by V.VENDOR,R.ADDRNUM, V.VNAMEL, R.AADDR1, R.AADDR2,P.BILLTO,R.ACITY, R.ASTATE, "
SQL = SQL & "R.AZIPCODE,R.VASST1,R.APHONE,L.LETTING, L.CALL,L.LCONTID,Q.CPROJNUM,Q.CFACSSUP,Q.CCNTY1 "
SQL = SQL & "ORDER BY Q.CPROJNUM DESC

 

[dvannoy]

I would use a stored procedure to execute the code and display the results. SP's are typically the best way to go.

 

[kebele]

can you show me how i can change this to sp?

 

[dvannoy]

you should post this in the sql forum.

all you would do is create the sp

e.g.

Create Procedure SPName
as
Select blaaaaaa from blaaaa
order by blaaaa

then execute the sp from your asp.net form. search around for examples on how to execute a sp from your asp.net form. there are tons of examples out there. once you get some of that code post back if you have problems.