Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Calling at Stored Proc (DHL)

    I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm confused...

    You say "Have an SQL code" in your post, does that mean that you have a Transact-SQL script? This is something that you'd run using either OSQL.EXE, SQLCMD.EXE, or one of the windows tools that allows you to execute SQL scripts.

    If not, what language are you using? C++ is very different from VB, and neither of them is much like Perl... The way that you access the database engine is radically different in each of these languages.

    Coach us a bit, and I'm sure that someone can help you!

    -PatP

  3. #3
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Calling Stored Proc

    Pat, Here is part of my code:
    Select
    m.member
    m.member_no
    s.Flag

    FROM member_table as M

    LEFT JOIN Status_table as S
    ON S.member_no = M.member_no


    Before I can use the Status Table, a stored proc need to process to update the table. I want to start the stored proc to update the table then run the code. Is there a way to add in code to kick off the stored proc then when it's done updating the Status_Table finish to rest of my code.
    Thanks for the help, David

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm obviously not asking the correct question, so I'll try again. What language are you using to write the progam that the user will execute? Can you show me a snippet of code around where you think you want to put the SELECT statement from your last post?

    -PatP

  5. #5
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Calling Stored Proc

    Running on SQL Server 2005. Both using basic SQL code. The stored Prod process the follwoing Code:
    Stored Proc:
    TRUNCATE TABLE dbo.Status_table

    ---- Y flag
    INSERT INTO dbo.Status_table

    SELECT
    M.Member_no,
    'Y' AS 'FLAG'
    FROM CMC_MEPE AS M

    WHERE M.MEPE_ELIG_IND = 'Y'
    AND M.MEPE_EFF_DT <= GETDATE()
    AND M.MEPE_TERM_DT >= GETDATE()

    This code is a stored Proc that I need to run to update table.
    This stored proc is not scheduled. I need to update the Status_table before I run the report.

    This is the SQL code for my report.
    Select
    m.member
    m.member_no
    s.Flag

    FROM member_table as M

    LEFT JOIN Status_table as S
    ON S.member_no = M.member_no

    Is there a way to get the Stored Proc to run so the table is updated before I run the code for the report. All this take place on the Sql Server.
    Thank you,
    David

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, that was the point that I needed!

    Yes, simply use something like:
    Code:
    EXECUTE myProcedure
    ...and you should be on your way!

    -PatP

  7. #7
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Calling a Stored Prod

    Thank you, David

  8. #8
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Calling a Stored Prod

    Pat, Can I call (or execute) this stored prod from a view. Is there a way when you are creating the view to have the code to execute the stored prod as part of the SQL I am using for the view. Thank you David

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, views do not support code execution for a number of reasons, although Microsoft does reserve the right to change that in future versions of the product (probably via a new type of trigger).

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation, but a stored procedure can be a data source, so you could create a new procedure that executed whatever procedures that you needed to execute, then performed a SELECT operation to return data to the caller of your procedure. This is logically the same as a view that executes code, it is just a different way of implementing the process.

    -PatP

Posting Permissions

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