SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist kuhajeya's Avatar
    Join Date
    Sep 2003
    Posts
    486
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    when to use stord procedures?

    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?

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    afrika, that's a very nice answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy, thank U Sir !


    well,....
    Quote Originally Posted by r937
    afrika, that's a very nice answer


  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by leblanc
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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?

    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

    Code:
    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

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.

  9. #9
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by leblanc
    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.

  11. #11
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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?

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

    Afrika

  13. #13
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah i'll look for one hold
    Code:
    <%@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>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •