CODEWITHSUNDEEP
sql-server-2008c#-4.0stored-proceduresado.net
Hubert Solecki
Hubert Solecki

Reputation: 2771

Cannot get output from a stored procedure

I have a stored procedure which returns me the identity of the added row, as below:

Create Procedure sp_ADD_CONTACT_EXTRANET_CLIENT
 (
@NumCRPCEN nvarchar (255),
@nomContact nvarchar (255),
@prenomContact nvarchar (255),
@telFixe nvarchar (255),
@telPort nvarchar (255),
@mailContact nvarchar(255),
@idPhysique int output
)
as 
Begin
    INSERT INTO T_Physique values (.....)
    SET @idPhysique = @@IDENTITY
    RETURN @idPhysique
End

Now I would like to get the output in ADO.NET and I've tried a lot of things but the last one is :

Requeteadd.Parameters.Add("@idPhysique", SqlDbType.Int).Direction = ParameterDirection.Output;
            Requeteadd.ExecuteNonQuery();
            int IdPhysique = (int)Requeteadd.Parameters["@idPhysique"].Value;

But it shows me an error where it says that returned value is null. Does anyone have an idea ?

Upvotes: 0

Views: 380

Answers (2)

Hubert Solecki
Hubert Solecki

Reputation: 2771

Like @RBarryYoung said, the solution is as below:

Create Procedure sp_ADD_CONTACT_EXTRANET_CLIENT
(
@NumCRPCEN nvarchar (255),
@nomContact nvarchar (255),
@prenomContact nvarchar (255),
@telFixe nvarchar (255),
@telPort nvarchar (255),
@mailContact nvarchar(255),
@idPhysique int output
)
as 
Begin
    INSERT INTO T_Physique values (1, @nomContact, @prenomContact, (Select AdrLngId from T_Adresse where AdrStrRC = '74003'), 'T', 2, null, null, null, null, null, null, null, null, '*', @telFixe, @telPort, null, null, null, 1, @mailContact, null, null, null, null, null)
    SET @idPhysique = @@IDENTITY

End 
RETURN @idPhysique

the return of the output has to be after the End. For information, in ADO.NET c#, I'm getting the returned value by doing this :

Requeteadd.Parameters.Add("@idPhysique", SqlDbType.Int).Direction = ParameterDirection.Output;
            Requeteadd.ExecuteNonQuery();
            int IdPhysique = (int)Requeteadd.Parameters["@idPhysique"].Value;

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Do not use RETURN to output data - this is for error / status codes, and is limited to INT, so as soon as you need to "return" other data types, you need to do something different. You have an output parameter, so why not use it? Also, use SCOPE_IDENTITY(), not @@IDENTITY, since the latter can be affected by triggers and not necessarily give you the identity value you just generated. Finally, always use the schema prefix when creating / referencing objects, and never use the sp_ prefix. You can use usp_ instead but what values does this add, really?

CREATE PROCEDURE dbo.usp_ADD_CONTACT_EXTRANET_CLIENT
  @NumCRPCEN nvarchar (255),
  @nomContact nvarchar (255),
  @prenomContact nvarchar (255),
  @telFixe nvarchar (255),
  @telPort nvarchar (255),
  @mailContact nvarchar(255),
  @idPhysique int output
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.T_Physique values (.....);
    SET @idPhysique = SCOPE_IDENTITY();
END
GO

Upvotes: 1

Related Questions