0

I'm working on a cascading insertion where a stored procedure should return an id of the inserted row. This would not be a problem if the id of the table was an int. However, the id is a varchar and therefore I cannot use SCOPE_IDENTITY().

This is my procedure so far: 

CREATE PROCEDURE NEW_ARTICLE
   @id varchar(50) OUTPUT, 
   @name varchar(100),
   @articleNr varchar(50),
   @gategory varchar(50),
   @containerId varchar(50),
   @contPerContainer int,
   @pictureId varchar(50) 
AS 
   SET NOCOUNT OFF

   INSERT INTO nextlabel.[Article] (name, article_nr, category, container_id, count_per_container, picture_id ) 
   VALUES (@name, @articleNr, @gategory, @containerId, @contPerContainer, @pictureId)

   SET @id = SCOPE_IDENTITY()

Where the last row is not correct since the column id is a varchar.

How can I return the id?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Victor Axelsson
  • 1,420
  • 1
  • 15
  • 32

1 Answers1

3

Try this:

CREATE PROCEDURE NEW_ARTICLE
  @id varchar(50) OUTPUT, 
  @name varchar(100),
  @articleNr varchar(50),
  @gategory varchar(50),
  @containerId varchar(50),
  @contPerContainer int,
  @pictureId varchar(50) 
AS 
SET NOCOUNT OFF
SET @id = newid()
INSERT INTO nextlabel.[Article] (id, name, article_nr, category, container_id, count_per_container, picture_id)
VALUES (@id, @name, @articleNr, @gategory, @containerId, @contPerContainer, @pictureId)

GO
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • This worked perfect. Your the man! Thanks a lot. Another question: is the GO really necessary? What does it do? (I'm new to working with procedures). – Victor Axelsson May 10 '14 at 09:55
  • It is default batch separator set up in SSMS. – Hamlet Hakobyan May 10 '14 at 09:58
  • see http://stackoverflow.com/questions/3701147/in-sql-server-when-should-you-use-go-and-when-should-you-use-semi-colon and http://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio – AjV Jsy May 10 '14 at 10:18