Optimize Store Procedure routine

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • inadsad@hotmail.com
    #1

    Optimize Store Procedure routine

    Dear Group,
    I have a store procedure with 12 input parameter and 2 output
    parameters I'm wondering what's the best way to simplify the
    following routine. I'd like to eliminate extra line of codes, do I
    need to define input direction for each input parameter? I'm using
    VS2005. I'd appreciate any useful feedback.

    Thank You
    Ian Nads
    -------------------------------------------
    Sqlparam = SqlComm.Paramet ers.Add(New
    SqlClient.SqlPa rameter("@CustI D", SqlDbType.Int))
    Sqlparam.Direct ion = ParameterDirect ion.Input
    Sqlparam.Value = CustomerID

    Sqlparam = SqlComm.Paramet ers.Add(New
    SqlClient.SqlPa rameter("@inAdd r1", SqlDbType.VarCh ar))
    Sqlparam.Direct ion = ParameterDirect ion.Input
    Sqlparam.Value = Address1

    Sqlparam = SqlComm.Paramet ers.Add(New
    SqlClient.SqlPa rameter("@inAdd r2", SqlDbType.VarCh ar))
    Sqlparam.Direct ion = ParameterDirect ion.Input
    Sqlparam.Value = Address2

    ...

    Sqlparam = SqlComm.Paramet ers.Add(New
    SqlClient.SqlPa rameter("@ID", SqlDbType.int))
    Sqlparam.Direct ion = ParameterDirect ion.Output

    Sqlparam = SqlComm.Paramet ers.Add(New
    SqlClient.SqlPa rameter("@ID2", SqlDbType.int))
    Sqlparam.Direct ion = ParameterDirect ion.Output

    ' excecute store procedure
    SqlComm.Execute NonQuery()
    ID = SqlComm.Paramet ers(13).Value
    ID2 = SqlComm.Paramet ers(14).Value

    -------------------------------------------
    Tags: None
    • Lloyd Sheen
      #2
      Re: Optimize Store Procedure routine


      <inadsad@hotmai l.comwrote in message
      news:62b02ac0-5caa-41f5-af87-2acedcfa911a@s5 0g2000hsb.googl egroups.com...
      Dear Group,
      I have a store procedure with 12 input parameter and 2 output
      parameters I'm wondering what's the best way to simplify the
      following routine. I'd like to eliminate extra line of codes, do I
      need to define input direction for each input parameter? I'm using
      VS2005. I'd appreciate any useful feedback.
      >
      Thank You
      Ian Nads
      -------------------------------------------
      Sqlparam = SqlComm.Paramet ers.Add(New
      SqlClient.SqlPa rameter("@CustI D", SqlDbType.Int))
      Sqlparam.Direct ion = ParameterDirect ion.Input
      Sqlparam.Value = CustomerID
      >
      Sqlparam = SqlComm.Paramet ers.Add(New
      SqlClient.SqlPa rameter("@inAdd r1", SqlDbType.VarCh ar))
      Sqlparam.Direct ion = ParameterDirect ion.Input
      Sqlparam.Value = Address1
      >
      Sqlparam = SqlComm.Paramet ers.Add(New
      SqlClient.SqlPa rameter("@inAdd r2", SqlDbType.VarCh ar))
      Sqlparam.Direct ion = ParameterDirect ion.Input
      Sqlparam.Value = Address2
      >
      ...
      >
      Sqlparam = SqlComm.Paramet ers.Add(New
      SqlClient.SqlPa rameter("@ID", SqlDbType.int))
      Sqlparam.Direct ion = ParameterDirect ion.Output
      >
      Sqlparam = SqlComm.Paramet ers.Add(New
      SqlClient.SqlPa rameter("@ID2", SqlDbType.int))
      Sqlparam.Direct ion = ParameterDirect ion.Output
      >
      ' excecute store procedure
      SqlComm.Execute NonQuery()
      ID = SqlComm.Paramet ers(13).Value
      ID2 = SqlComm.Paramet ers(14).Value
      >
      -------------------------------------------
      If you check in the Object explorer you will see Input is the default for a
      SqlParameter so you will not need that line unless you are dealing with a
      value other than Input

      LS

        Comment

        Previous template Next
        Working...