Returnvalue problem

[marcarls]

Hi,
Having (probably) a newbie problem with returnvalues from a stored procedure.

I am calling this proceure from within another procedure:

CREATE PROCEDURE PartsCap_GetSystemID

@SystemName varchar(50)
AS

--System.ID is a uniqueidentifier.
Select System.ID from System
Where System.Name = @SystemName
GO

The real procedure are much larger but this produces the same problem, I do the call to this procedure from within another procedure:

CREATE PROCEDURE PartsCap_Exec
@SystemName as varchar(50)
AS
Declare @SystemID uniqueidentifier

EXEC @SystemID = PartsCap_GetTextTypeID @SystemName
print @SystemID
GO

The problem I am facing is that the variable @SystemID has the value "0".

If I do:
EXEC PartsCap_GetSystemID @SystemName
the result is fine..

I have also played around a bit with RETURN but that only complains that it cannot convert a uniqeidentifier to an int.

Anny ideas?
Thankyou for taking your time..

 

[EdwinGene]

marcarls,

I set up a quick test and got the int incompatible with uniqueidentifier message. Then I rewrote the stored procedures to return the uniqueidentifier with an output paramater and the message disappeared. Try the following and see if it helps:


CREATE PROCEDURE PartsCap_GetSystemID
@in_SystemName varchar(50),
@out_SystemID uniqueidentifier Output
AS

--System.ID is a uniqueidentifier.
Select @out_SystemID = System.ID from System
Where System.Name = @in_SystemName
GO


CREATE PROCEDURE PartsCap_Exec
@SystemName as varchar(50)
AS
Declare @SystemID uniqueidentifier

EXEC PartsCap_GetSystemID @in_SystemName = @SystemName, @out_SystemID = @SystemID Output
print @SystemID
GO