Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Execute Stored Proc and then return a value

    ok I have a stored procedure in my MS-SQL Server database.
    It looks something like this.....

    CREATE PROCEDURE updatePCPartsList
    (
    @Description varchar(255),
    @ManCode varchar(255),
    @ProdCode varchar(255),
    @Price decimal(6,2),
    @Comments varchar(255)
    )
    AS

    declare @IDFound bigint
    declare @LastChangedDate datetime

    select @LastChangedDate = GetDate()
    select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode

    if @IDFound > 0
    begin
    update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
    end
    else
    insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
    GO

    It executes fine so I know i've done that much right....
    But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable

    I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
    Someone help?

  2. #2
    Join Date
    Jul 2004
    Posts
    12
    You can add an extra parameter to the call, which you can use as an output variable.

    Try the following:

    CREATE PROCEDURE updatePCPartsList
    (
    @Description varchar(255),
    @ManCode varchar(255),
    @ProdCode varchar(255),
    @Price decimal(6,2),
    @Comments varchar(255),
    @LastChangedDate datetime output
    )
    AS
    declare @IDFound bigint
    select @LastChangedDate = GetDate()
    select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
    if @IDFound > 0
    begin
    update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
    end
    else
    insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
    GO



    When you call the proc you'll need to capture the output to a variable of your choice so do something like:

    DECLARE @LCDate datetime
    EXEC updatePCPartsList
    @Description='Dummy Item',
    @ManCode='12121',
    @ProdCode='54321',
    @Price=123,
    @Comments='blah blah',
    @LastChangedDate=@LCDate OUTPUT


    then @LCDate should contain the result.

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Ok - I kinda see what your doing but i'm not sure if it's what i'm looking for.....

    First let me explain my situation a bit more.....
    I'm writing an application (specifically an excel spreadsheet using vba) in which I need to execuite that stored procedure and then return the @LastChangedDate value to a variable in my VBA.

    Can I use your example still?
    Would your additional bit of SQL be a second Stored Proc? And would I call that - not my first proc?

  4. #4
    Join Date
    Jul 2004
    Posts
    12
    Ah, I assumed you were wanting the result in a SQL local variable, not passed to Excel. I doubt you'd be able to use that modification for your needs

    Sadly, I'm no VBA wizard but I'd assume you need to alter your original stored proc to have a "SELECT @LastChangedDate" at the end so it prints the output you want into the resultset.

    Assuming you've used the SQLExecuteQuery command to run the query you should be able to use SQLRetrieve to get the resultset and extract the value from there.

    SQLRetrieve allows you to specify an Excel cell range to put the results in and a max rows/cols value (which you'd have set to 1).

    The VBA help in Excel has a nice reference for SQLExecuteQuery and SQLRetrieve, with a few examples that may help.

Posting Permissions

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