Returning UniqueIdentifier From INSERT Stored Procedure

• r.sacher

  SSC Veteran

  Points: 221

  More actions

  October 9, 2008 at 2:22 am

  #194109

  I cannot return (output) the newly created uniqueidentifier from a stored procedure

  OUTPUT is always but new record gets inserted and new GUID is created

  My Table:

  CREATE TABLE [dbo].[tbl_Clients](

  [ClientID] [uniqueidentifier] NULL,

  [ClientName] [varchar](250) NULL,

  [ClientEnabled] [bit] NULL

  )

  My StP:

  CREATE PROCEDURE sp_ClientCreate

  @in_ClientName varchar(250) = "New Client 123",

  @in_ClientEnabled bit,

  @out_ClientId uniqueidentifier OUTPUT

  AS

  SET @out_ClientId = NEWID();

  INSERT

  INTO tbl_Clients(ClientId, ClientName, ClientEnabled)

  VALUES(

  @out_ClientId,

  @in_ClientName,

  @in_ClientEnabled)

  The Output (running in VS 2008):

  Running [dbo].[sp_ClientCreate] ( @in_ClientName = my Name, @in_ClientEnabled = true, @out_ClientId = NULL>).

  (1 row(s) affected)

  (0 row(s) returned)

  @out_ClientId =

  @RETURN_VALUE = 0

  Finished running [dbo].[sp_ClientCreate].

  But my Table now contains:

  9a8cd618-e115-4697-81e0-e7ae08307a97my NameTrue

  Thanks for help,

  Reinhard

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  October 9, 2008 at 3:09 am

  #882756

  You need to add the OUTPUT keyword to the @out_ClientId parameter when you are calling the stored procedure.