CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Victor Axelsson
Victor Axelsson

Reputation: 1466

SQL Server Stored Procedure - Return varchar id of inserted row

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?

Upvotes: 0

Views: 1432

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 3

Related Questions