I have these two stored procedures that work as expected.

here ================
Create PROCEDURE [dbo].[GetCustomerIDFromCustomerDeviceByGUID]
-- Add the parameters for the stored procedure here
@gUID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
DECLARE @customerID int;
SELECT @customerID = 0;
SELECT @customerID = [Jahasma.Customer.Device].[CustomerID]
FROM [Jahasma.Customer.Device]
WHERE ([Jahasma.Customer.Device].[GUID] = @gUID);
SELECT @customerID as 'CustomerID'
END

and here ====================

Create PROCEDURE [dbo].[Jahasma_GetCustomerInjuryByCustomerID]
@customerID smallint
AS
BEGIN

SET NOCOUNT ON

SELECT
[jahasma.Customer.Injury].[InjuryID] AS 'InjuryID',
[jahasma.Customer.Injury].[InjuryDescription] AS 'InjuryDescription',
[jahasma.Customer.Injury].[LastUpdateUTC] AS 'LastUpdateUTC'
FROM [dbo].[Jahasma.Customer.Injury] [jahasma.Customer.Injury]
WHERE [jahasma.Customer.Injury].[CustomerID]=@customerID

SET NOCOUNT OFF
END


Can someone please help me write a Third Procedure that will execute
the two procedures I have above
I want to call the first SP and get back the CustomerID. Then call the
second SP and return the recordset.

It should go something like this....


CREATE PROCEDURE [dbo].[Jahasma_GetCustomerInjuryByCustomerDeviceGuid]
@gUID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
-- =============================================
-- Create a customerID variable
-- Call Jahasma_GetCustomerIDFromCustomerDeviceByGUID
-- and remember the CustomerID
-- =============================================
DECLARE @customerID smallint
@customerID = EXECUTE
[dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@gUID = @gUID;

-- =============================================
-- Call Jahasma_GetCustomerInjuryByCustomerID
-- using the Customer ID and the parameter
-- =============================================

EXECUTE [dbo].[Jahasma_GetCustomerInjuryByCustomerID]
@customerID = @customerID;

SET NOCOUNT OFF
END

[quoted text, click to view]

Use an OUTPUT parameter in the first procedure instead of returning a
result set.

CREATE PROCEDURE [dbo].[GetCustomerIDFromCustomerDeviceByGUID]
-- Add the parameters for the stored procedure here
@gUID UNIQUEIDENTIFIER,
@customerID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @customerID = 0;
SELECT @customerID = [Jahasma.Customer.Device].[CustomerID]
FROM [Jahasma.Customer.Device]
WHERE ([Jahasma.Customer.Device].[GUID] = @gUID);
END



CREATE PROCEDURE [dbo].[Jahasma_GetCustomerInjuryByCustomerDeviceGuid]
@gUID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
-- =============================================
-- Create a customerID variable
-- Call Jahasma_GetCustomerIDFromCustomerDeviceByGUID
-- and remember the CustomerID
-- =============================================
DECLARE @customerID smallint
EXECUTE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@gUID = @gUID, @customerID OUTPUT;


-- =============================================
-- Call Jahasma_GetCustomerInjuryByCustomerID
-- using the Customer ID and the parameter
-- =============================================


EXECUTE [dbo].[Jahasma_GetCustomerInjuryByCustomerID]
@customerID = @customerID;


SET NOCOUNT OFF
END

Chris,

Thank-you for your reply.
Following your instructions I resolved the issue, but I struck a few
problems along the way, which I managed to resolve. However perhaps
there was another way to resolve this.

I changed the first SP to use the OUTPUT parameter - works fine.

The second SP I copied as you suggested:
[quoted text, click to view]
and recieved the following error
=========
Msg 119, Level 15, State 1, Procedure
Jahasma_GetCustomerInjuryByCustomerDeviceGuid, Line 19
Must pass parameter number 2 and subsequent parameters as '@name =
value'. After the form '@name = value' has been used, all subsequent
parameters must be passed in the form '@name = value'.
=========

I then tried
EXECUTE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@gUID = @gUID,
@customerID = 0 OUTPUT;
but that also created an error
===========
Msg 179, Level 15, State 1, Procedure
Jahasma_GetCustomerInjuryByCustomerDeviceGuid, Line 21
Cannot use the OUTPUT option when passing a constant to a stored
procedure.
===========

I finally resolved the problem of reversing around the Parameters, so
that the first SP looked like this

CREATE PROCEDURE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@customerID SMALLINT OUTPUT,
@gUID UNIQUEIDENTIFIER

and the call was like this
EXECUTE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@customerID OUTPUT,
@gUID = @gUID;

So the question is, could I have resolved these issues without changing
the parameters around?

TIA

[quoted text, click to view]

Yes, the error is saying that once you start passing parameters by name
(e.g. @param1 = @value1), all subsequent parameters need to be passed
by name as well (as opposed to by position).

So by passing @customer_id by name, you will get around that error:

EXECUTE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@gUID = @gUID, @customerID = @customerID
OUTPUT;

Passing all parameters by position will also get around it:

EXECUTE [dbo].[Jahasma_GetCustomerIDFromCustomerDeviceByGUID]
@gUID, @customerID OUTPUT;

Chris