Get Output After Insert

Question

Post reply

Get Output After Insert

David-155102

SSCarpal Tunnel

Points: 4361
More actions
April 1, 2009 at 9:56 am

#129575

Ok, I've been looking at this for ages and just cannot get what I want to work!
Here's my table:
CREATE TABLE [dbo].[ForecastPartsH](
[header] [uniqueidentifier] NOT NULL,
[infoType] [nchar](3) NULL,
[prodSite] [nchar](20) NULL,
[version] [nchar](15) NULL,
[savedBy] [nchar](20) NULL,
[department] [nchar](10) NULL,
[dateUploaded] [datetime] NULL
) ON [PRIMARY]
Here's my stored proc:
CREATE PROCEDURE [dbo].[Add_Header]
@InfoTypeNCHAR(3),
@ProdSiteNCHAR(20),
@VersionNCHAR(15),
@SavedByNCHAR(20),
@DepartmentNCHAR(10),
@HeaderIDUNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ForecastPartsH VALUES (NEWID(), @InfoType, @ProdSite, @Version, @SavedBy, @Department, GETDATE())
END
What I want is for the NEWID() value to be returned when the stored proc is executed. Sounds simple and I feel really stupid for having to ask but please remind me how it's done!
Thanks
David

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi David

You cannot use RETURN for guids. You have to define an additional parameter (e.g. @Id) as OUPUT parameter:

CREATE PROCEDURE [dbo].[Add_Header]

@Id UNIQUEIDENTIFIER OUTPUT,

@InfoType NCHAR(3),

@ProdSite NCHAR(20),

@Version NCHAR(15),

@SavedBy NCHAR(20),

@Department NCHAR(10),

@HeaderID UNIQUEIDENTIFIER OUTPUT

AS

BEGIN

SET NOCOUNT ON;

SELECT @Id = NEWID()

INSERT INTO ForecastPartsH VALUES (@Id, @InfoType, @ProdSite, @Version, @SavedBy, @Department, GETDATE())

END

Greets

Flo

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

Thanks for the response Flo.

I now have a different problem with the output value! I am calling the sp using the following:

declare @ID uniqueidentifier

exec dbo.Add_Header @ID, 'A','B','C','D','E'

select @ID as ID

The row is inserted but NULL is returned for the @ID parameter?!

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi David

You have to define the @Id also for OUTPUT when callin':

declare @ID uniqueidentifier

exec dbo.Add_Header @ID OUTPUT, 'A','B','C','D','E'

select @ID as ID

Greets

Flo

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 4

Florian Reischl (4/1/2009)
Hi David
You have to define the @Id also for OUTPUT when callin':
declare @ID uniqueidentifier
exec dbo.Add_Header @ID OUTPUT, 'A','B','C','D','E'
select @ID as ID
Greets
Flo

Ah! Thank you Flo, much appreciated!