when to use stord procedures?

[kuhajeya]

hi buddies,

when would you go for using stored procedures? which is the best approach, when you write applications in what situations would you go for stored procedures rather than embedding queries in application?

[afrika]

SP are very good,
they are a useful way of building a 3tier system, they can accept input from the calling application (ie a web page, vb app) and pass then to the db, or do some useful work and then return a return value to the web page.

Taht way, users dont interact directly with your db.

It allows for security, speed and consistent programs. hence u dont have a write the same app for multiple pages, just call it in your page and it does your work and updates, selects, inserts or deletes basically does WORK

hope this helps
Afrika

[r937]

afrika, that's a very nice answer

[afrika]

Hi Rudy, thank U Sir !


well,....

afrika, that's a very nice answer

[leblanc]

can someone give an example application? I've made sense of afrika statement but does anyone have an application that uses SP in the manner described by afrika.

I guess I'm a visual learner. - sorry

I guess the hardest part to grasp is in what situation would you not want a user to interact with the db. (maybe because i've only designed for the web? - I seperate functions depending on logged in credentials)

Which statement types are most commonly associated with stored procedures: inserts,updates,deletes, or selects

my guess is deletes... maybe to create a cascade deletion algorithm?

[r937]

my guess is deletes... maybe to create a cascade deletion algorithm?

actually, that'd be the least likely

cascading deletes are built in, and you would only write a stored procedure to do this if you wanted to "reinvent the wheel" for some reason

here's an example of how a stored proc would improve efficiency

supposed you wanted to insert fourteen rows of data, and for each row, you need to provide two values

write a stored procedure to accept 28 parameters, use stored proc logic to formulate those values into an INSERT statement which you execute in a loop fourteen times

why is this better than simply calling the database yourself fourteen times?

because calling the database is the largest chunk of overhead in that scenario

it's like if you had to move fourteen boxes from new york to los angeles, you could haul one box at a time and your truck would make fourteen cross-country trips, or else you could send all fourteen boxes in one truckload

admittedly, it might take a wee bit longer to load and unload fourteen at once than a single box fourteen times, but overall, you will finish the job a lot faster with just one trip

same with calling the database, one call that does fourteen things is going to be a lot faster than fourteen calls that only do one thing

[afrika]

can someone give an example application? I've made sense of afrika statement but does anyone have an application that uses SP in the manner described by afrika.

I guess I'm a visual learner. - sorry

I guess the hardest part to grasp is in what situation would you not want a user to interact with the db. (maybe because i've only designed for the web? - I seperate functions depending on logged in credentials)

Which statement types are most commonly associated with stored procedures: inserts,updates,deletes, or selects

my guess is deletes... maybe to create a cascade deletion algorithm?

Hope u do understand what Rudy is saying above ?

Well we all need visual tutors, to makes things fast and simple to grasp. I learnt both ways.

It also works like this

Web app >>>>>>>>>>>>   Stored Proceedures  >>>>>>>> backend db

a web page, simple
1. Declares the variables to be passed to the SP
2. Calls the name of the Sp

what front end app are using ?

I could give you a simple explanaiton in asp if it is

afrika

[kuato]

here's an example of how a stored proc would improve efficiency

supposed you wanted to insert fourteen rows of data, and for each row, you need to provide two values

write a stored procedure to accept 28 parameters, use stored proc logic to formulate those values into an INSERT statement which you execute in a loop fourteen times

why is this better than simply calling the database yourself fourteen times?

because calling the database is the largest chunk of overhead in that scenario

Or use batching from the application level so you can still make one trip to do 14 inserts. Java has excellent batch support.

[leblanc]

same with calling the database, one call that does fourteen things is going to be a lot faster than fourteen calls that only do one thing

great example.

So basically if in the application code there exists a for loop which involves a db call, a stored procedure is the way to go?

for loops normally exist for select/insert statements.. so i'm guessing that is where SP are mostly used than? (as 'cascade delete/update are built in')

I'll remember that next time i'm about to write a for loop with a db call.

-lm

[kuato]

great example.

So basically if in the application code there exists a for loop which involves a db call, a stored procedure is the way to go?

for loops normally exist for select/insert statements.. so i'm guessing that is where SP are mostly used than? (as 'cascade delete/update are built in')

I'll remember that next time i'm about to write a for loop with a db call.

-lm

See above post If you can batch your statements at the application level then you can do the operation in one trip, you aren't forced to use an SP in that example.

It all really depends on what you need out of your SP. If it's a complex operation that that updates many different tables then it's nice to have that logic in the database where many different clients(say one java desktop app, one php webapp) can call the same SP without duplicating the logic at the application level.

For mass inserts into one table though, it's sometimes better to batch it in the app especially you only have one type of app(i,e it's just Java). On the other hand if you were doing an upsert(update first then insert if the rowcount is 0) on multiple rows I'd rather put that in an SP proc and use the MERGE INTO( if it's Oracle).

To use an SP or not really varies depending on your needs.

[Ren]

One of the better examples of the neatness of using stored procedures is using the nested set model for trees and hierarchies.

The client application doesnt even have to be aware you using nested sets, and never have to return the left & right columns to the client.

[afrika]

can someone give an example application? I've made sense of afrika statement but does anyone have an application that uses SP in the manner described by afrika.

I guess I'm a visual learner. - sorry

I guess the hardest part to grasp is in what situation would you not want a user to interact with the db. (maybe because i've only designed for the web? - I seperate functions depending on logged in credentials)

Which statement types are most commonly associated with stored procedures: inserts,updates,deletes, or selects

my guess is deletes... maybe to create a cascade deletion algorithm?

hi leblanc,
All said and done do you now have a graphic overview and clear understanding ???
Afrika

[nellyihu]

yeah i'll look for one hold

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/cncoebenin.asp" -->
<%
Dim rstFormNumber__MMColParam
rstFormNumber__MMColParam = "1"
If (Request.Form("Pin") <> "") Then
  rstFormNumber__MMColParam = Request.Form("Pin")
End If
%>
<%
if request.Form("submit")="submit" then
     session("pin")=Request.form("Pin")
    'generate a form application number first
	 Dim rstFormNumber, appno, frmno
	Dim rstFormNumber_numRows
	Set rstFormNumber = Server.CreateObject("ADODB.Recordset")
	rstFormNumber.ActiveConnection = MM_cncoebenin_STRING
	rstFormNumber.Source = "SELECT AccessID  FROM dbo.AccessCodes  WHERE accesscode = '" & request.form("Pin") & "' AND matricorregno IS NULL"
	rstFormNumber.CursorType = 0
	rstFormNumber.CursorLocation = 2
	rstFormNumber.LockType = 1
	rstFormNumber.Open()
	rstFormNumber_numRows = 0
	if rstFormNumber.eof=true and rstFormNumber.bof=true then'movesn to the last record in the table to get the tnum
	   'do nothing
	else
	    appno=rstFormNumber("AccessID")
    end if
	'now increament appno by one to get the new appno
	appno=cint(appno)+1
	session("frmno")="COLBEN-YRONE100"& appno  'send this to the dbase
	rstFormNumber.close()
	'do main validation now this is where the sp is called
	set cmdPin1 = Server.CreateObject("ADODB.Command")
	cmdPin1.ActiveConnection = MM_cncoebenin_STRING
	cmdPin1.CommandText = "dbo.STP_STUDENTSPAYMENTSCHECK"
	cmdPin1.CommandType = 4
	cmdPin1.CommandTimeout = 0
	cmdPin1.Prepared = true
	cmdPin1.Parameters.Append cmdPin1.CreateParameter("@RETURN_VALUE", 3, 4)
	cmdPin1.Parameters.Append cmdPin1.CreateParameter("@Accesscode", 200, 1,50,Request.form("Pin"))
	cmdPin1.Parameters.Append cmdPin1.CreateParameter("@frmno", 200, 1,50,session("frmno"))
	cmdPin1.Execute()
	'RESPONSE.Write cmdPin.parameters("@RETURN_VALUE").value
	select case cmdPin1.parameters("@RETURN_VALUE").value
	  case 1
		 'response.Write("VALID PIN CODE")
		 session("yr1")="true"
		 server.Transfer("yearoneapplication.asp")
		 'response.redirect("yearone/yearoneapplication.asp")
	  case 2
		 response.Write("INVALID PIN CODE")
	end select
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head>
<title>WELCOME TO&nbsp; ADMISSION FORM PURCHASE</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.style51 {FONT-SIZE: 10px; COLOR: #0000ff
}
.style52 {COLOR: #333333; FONT-FAMILY: Verdana, Arial, Helvetica, sans-serif
}
-->
</style>
<link href="coe.css" rel="stylesheet" type="text/css">
<script language="javascript">
function validate()
{
  if (document.form1.Pin.value=="")
   {
     window.alert("Please Enter A Pin Number");
	 return false;
   }
   if (document.form1.surname.value=="" || document.form1.firstname.value=="")
   {
     window.alert("Please Your First Or Last Names");
	 return false;
   }

   return true;
}
</script>
</head>

<body background="Images/bg.gif">
<TABLE height="100%" cellSpacing=0 cellPadding=0 width="100%" border=0>
  <TBODY>
    <TR>
      <TD width="100%" background="Images/bg.gif">
        <DIV align=center>&nbsp;
            <p><br>
&nbsp;</p>
            <TABLE cellSpacing=0 cellPadding=0 width=828 border=0 style="border:1px solid black" height="117">
              <TBODY>
                <TR>
                  <TD width=20 bgColor=#CCCCCC height="41" background="images\top_bg.gif">&nbsp;</TD>
                  <TD width=783 bgColor=#CCCCCC height="24" background="images\top_bg.gif">
                    <table border="0" width="100%" id="table2" height="46">
                      <tr>
                        <td colspan="4">&nbsp;</td>
                      </tr>
                      <tr>
                        <td width="2%" height="22">
						<p align="center"> <img border="0" src="images\bullet_corner_red.gif" width="13" height="10"></td>
                        <td width="52%" height="22">
                          <p align="left"><b><font color="#FFFFFF">
							<span class="text">
							Year 1 Admission Request Form Purchase </span></font></b></td>
                        <td width="7%" height="22"></td>
                        <td width="38%" height="22">&nbsp;</td>
                      </tr>
                  </table></TD>
                  <TD width=23 bgColor=#CCCCCC height="41" background="images\top_bg.gif">&nbsp; </TD>
                </TR>
                <TR>
                  <TD width=20 height="30" bgColor=#CCCCCC>&nbsp; </TD>
                  <TD width=783 bgColor=#CCCCCC>
					<form action="index1.asp" method="POST" enctype="application/x-www-form-urlencoded" name="form1" onsubmit="return validate()">
                    <table width="93%"  border="0" align="center" cellpadding="0" cellspacing="0" id="tblEligibity">
                      <tr>
                        <td><table width="80%"  border="0" align="center" cellpadding="0" cellspacing="0">
                            <tr>
                              <td colspan="2">
								<p align="right"><SPAN
      class=style51><FONT face=Verdana size=2><A
      href="/index.htm">Exit</A></FONT></SPAN></td>
                            </tr>
                            <tr>
                              <td colspan="2"><p>&nbsp;</p></td>
                            </tr>
                            <tr>
                              <td>&nbsp;</td>
                              <td>&nbsp;</td>
                            </tr>
                          </table>
                            <TABLE borderColor=#cccccc width="55%" align=center bgColor=#cccccc border=1 style="border:2px solid silver">
                              <TBODY>
                                <TR>
                                  <TD bgcolor="#CCCCCC">
                                    <DIV align=center>
                                      <TABLE width="64%" bgColor=#cccccc border=0>
                                        <TBODY>
                                          <TR>
                                            <TD bgColor=#CCCCCC>
                                              <P style="MARGIN-TOP: 1px; MARGIN-BOTTOM: 1px" align=center>
												<span class="textSml">
												<u>
												<b>
												Application
												Forms </b></font><B>Payment</FONT></B>: - <B>
												Step 1</B></u></span></span></P></TD>
                                          </TR>
                                        </TBODY>
                                      </TABLE>
                                    </DIV>
                                    <P style="MARGIN-TOP: 1px; MARGIN-BOTTOM: 1px" align=center>&nbsp;</P>
                                    <P style="MARGIN-TOP: 1px; MARGIN-BOTTOM: 1px" align=center>&nbsp;</P>

                                  <TABLE cellSpacing=2 cellPadding=2 width=460 align=center>
                                    <TBODY>
                                      <TR vAlign=baseline bgColor=#ffffff>
                                        <TD noWrap align=right colSpan=3> <DIV align=center></DIV></TD>
                                      </TR>
                                      <TR vAlign=baseline bgColor=#ffffff>
                                        <TD noWrap align=right bgColor=#CCCCCC width="10">&nbsp;</TD>
                                        <TD noWrap align=right bgColor=#CCCCCC width="113"><span class="textSml">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Pin Code </span></FONT></TD>
                                        <TD bgColor=#CCCCCC width="317"> <INPUT name=Pin id="Pin22"
            style="BORDER-RIGHT: #808080 1px solid; BORDER-TOP: #808080 1px solid; TEXT-TRANSFORM: capitalize; BORDER-LEFT: #808080 1px solid; COLOR: #808000; BORDER-BOTTOM: #808080 1px solid; FONT-FAMILY: Verdana"
            size=25></TD>
                                      </TR>
                                      <TR vAlign=baseline>
                                        <TD noWrap align=right width="10">&nbsp;</TD>
                                        <TD noWrap align=right width="113">&nbsp;</TD>
                                        <TD width="317">
										&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
										<INPUT style="border:1px solid #666666; COLOR: #000000; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #808080; font-size:8pt" type=submit value="submit" name=submit onsubmit="return doit(this)">
                                          <input style="border:1px solid #666666; COLOR: #000000; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #808080; font-size:8pt" name="Cancel" type="reset" id="Cancel2" value="Cancel">
                                        </TD>
                                      </TR>
                                    </TBODY>
                                  </TABLE>
                                    <P align=right>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P>
                                    <P align=right></P></TD>
                                </TR>
                              </TBODY>
                          </TABLE></td>
                      </tr>
                    </table>
                    <div align="center"></div>
                  </form> </TD>
                  <TD width=23 bgColor=#CCCCCC>&nbsp;</TD>
                </TR>
                <TR>
                  <TD width=20 bgColor=#CCCCCC height="36" background="images\top_bg_1.gif">&nbsp;</TD>
                  <TD width=806 bgColor=#CCCCCC colspan="2" height="36" background="images\top_bg_1.gif">&nbsp;</TD>
                </TR>
              </TBODY>
            </TABLE>
            <p align="center"> <img border="0" src="images\nu_edlink.gif" width="104" height="46"><br>
            All Rights Reserved (c) Global Portal Services
      </DIV></TD>
    </TR>
  </TBODY>
</TABLE>
</body>
</html>