Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303

    Unhappy Unanswered: Stored Procedure

    hi,
    I dont how make a stored procedure. Anybody knews on how to do it pls help.
    e.x. I want to insert or update or delete a record in stored procedure way.


    Private Sub Command1_Click()
    help me. pls ...
    End Sub

  2. #2
    Join Date
    Nov 2004
    Posts
    108
    You should make the SP in the DataBase and then call it from VB

    If you want to make a simple SP and if you are using SQL Server you can use tools\wizards\database\create Stored procedure wizard if you want to make a more complex SP the you should post your question concerning to SP creation in the appropriate DB forum
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Tuenty,

    I have no idea on how to do it, can u give me a 1 sample code of your SP if it's ok for you. Yes I use Sql Server but it is posible to create a SP using VB codes?
    I want to create an SP to retreive customer using two field <name> and <location> and how to do it?

    Thanks...
    Last edited by popskie; 12-14-04 at 22:54. Reason: add another question

  4. #4
    Join Date
    Nov 2004
    Posts
    108
    Don't create the SP with VB

    After you create the SP in SQL Server call it from VB

    Code:
    rec.Open "YourSP param1, param2, ect", conn
    From BOL
    Code:
    How to create a stored procedure (Enterprise Manager)
    To create a stored procedure 
    
    Expand a server group, and then expand a server.
    
    
    Expand Databases, and then expand the database in which to create the procedure.
    
    
    Right-click Stored Procedures, and then click New Stored Procedure.
    
    
    Enter the text of the stored procedure. Press TAB to indent the text of a stored procedure. Press CTRL+TAB to exit the text box, or click an appropriate button.
    
    
    To check the syntax, click Check Syntax.
    
    
    To set the permissions, click Permissions. 
    
    ===============================
    
    Use a simple procedure with parameters 
    This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.
    
    USE pubs
    IF EXISTS (SELECT name FROM sysobjects 
             WHERE name = 'au_info' AND type = 'P')
       DROP PROCEDURE au_info
    GO
    USE pubs
    GO
    CREATE PROCEDURE au_info 
       @lastname varchar(40), 
       @firstname varchar(20) 
    AS 
    SELECT au_lname, au_fname, title, pub_name
       FROM authors a INNER JOIN titleauthor ta
          ON a.au_id = ta.au_id INNER JOIN titles t
          ON t.title_id = ta.title_id INNER JOIN publishers p
          ON t.pub_id = p.pub_id
       WHERE  au_fname = @firstname
          AND au_lname = @lastname
    GO
    
    The au_info stored procedure can be executed in these ways:
    
    EXECUTE au_info 'Dull', 'Ann'
    -- Or
    EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
    -- Or
    EXEC au_info 'Dull', 'Ann'
    -- Or
    EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
    
    Or, if this procedure is the first statement within the batch:
    
    au_info 'Dull', 'Ann'
    -- Or
    au_info @lastname = 'Dull', @firstname = 'Ann'
    -- Or
    au_info @firstname = 'Ann', @lastname = 'Dull'
    to err is human ; to really mess things up requires a computer

  5. #5
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Thank you so much...

Posting Permissions

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