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?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.|||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?|||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.