Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    Question Unanswered: Stored Procedure - doubt

    Hi
    I am fully confused with Stored Procedure. Because
    When i taking 500 recods from Database I wrote the query

    Select field1,field2,field3,field4.. from Table where fieldf1>100 and field2 like 'A%"

    Same query i wrote in Stored procedure . It is not showing any big difference between regular method and StoredProcedure.

    but some of the routines like inserting and updating in n number of times.... etc . Stored procedures are good.

    So, For taking recoeds from database should I use regular method or Stored Procedure Which one is Better some times i need to use inner join queryes also.

    Please clear my doubt.
    -- Prasad --

  2. #2
    Join Date
    Mar 2002
    Posts
    7
    Store Procedured (SP) is good when you want to build application with VB or other languages.

    If you call SP in your application it works faster then you do with regular method.

    For example if you want to retrieve data so you write "Select * from ...."
    You had better make it SP and you call it from your application

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Stored procedures should always be slightly faster than embedded sql as (after the first call) the plan will be cached.

    The main reason for always using them though is to abstract the database structure from the client.

    For your query
    Select field1,field2,field3,field4.. from Table where fieldf1>100 and field2 like 'A%'

    There may come a time when the table is too large to run this efficiently so you add a redundent field with the first letter of field2 and change the sp to
    Select field1,field2,field3,field4.. from Table where fieldf1>100 and fieldfirst = 'A

    You can do this all without affecting the client app.

    It also helps security as you don't have to give the user access to data - only to stored procs.

Posting Permissions

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