Insert and update multiple records via same stored procedure

Question

I created this stored procedure to go through all the records in the table comparing the id (primary key) if exists and the records changed, make the necessary changes & update the record.

If the id is not in the table then insert the record. This stored procedure compiles fine, but doesn't seem to work properly. Does this need a while loop?

ALTER PROCEDURE [dbo].[SMLineUpdate]
(
  @id [int],
  @Payroll_Id [int],
  @ProductCode nvarchar(255),
  @Description nvarchar (255),
  @Qty nvarchar(255)
)
AS
IF EXISTS (SELECT Id from Smline where @id = Id) BEGIN 
  update dbo.SmLine
    Set [Payroll_Id] = @Payroll_Id
    , ProductCode = @ProductCode
    , Description = @Description
    , Qty = @Qty
END ELSE BEGIN 
  INSERT INTO SmLine ([Payroll_Id], [ProductCode], [Description], [Qty])
    VALUES (@Payroll_Id, @ProductCode, @Description, @Qty)
END

This procedure can only handle a single row, If you want to handle multiple rows you need to use a different approach - like a table valued parameter. I'm guessing based on the syntax that this is SQL Server - take a look at [this answer](https://stackoverflow.com/a/52780490/3094533) posted by Aaron Bertrand. — Zohar Peled, Apr 01 '19 at 08:06
Define *dosent seem to work properly* please. Any error message? Any unexpected behaviour (if so, what is that behaviour ?) — Cid, Apr 01 '19 at 08:09
Thanks for all your suggestions, Vikram your code seems to work but when the id is not supplied at the front end on the vb side, I am getting a string conversion error, because it's trying to convert blank value when the Id is not supplied. Can this be handled from the sql stored procedure? "IF EXISTS(SELECT Id from Smline where Id =@id or Id > 0)" — Randy, Apr 01 '19 at 09:37
Your "@id" parameter is not optional. You must pass it in. (As mentioned) your update needs to have a where clause that filters on the @id (or you will update every row in your table). Your procedure is "single row" based (aka, it can ONLY update or insert one row at a time). If you want to handle multiple rows, you need different inputs. internet search "sql server shred xml" for examples. — granadaCoder, Apr 02 '19 at 07:16

score 0 · Answer 1 · answered Apr 01 '19 at 08:42

0

Your update query is missing a where condition

update dbo.SmLine
Set    [Payroll_Id] = @Payroll_Id
      ,ProductCode  = @ProductCode
      ,Description  = @Description
      ,Qty = @Qty
WHERE  Id = @Id      -- the query missed this where condition

answered Apr 01 '19 at 08:42

Squirrel

23,507
4
34
32

score 0 · Answer 2 · answered Apr 01 '19 at 08:47

IF EXISTS(SELECT Id from Smline where Id =@id)
BEGIN 
update dbo.SmLine
Set [Payroll_Id]= @Payroll_Id
,ProductCode= @ProductCode
,Description = @Description
,Qty = @Qty
WHERE  Id = @Id  
END
ELSE
BEGIN 
INSERT INTO SmLine ([Payroll_Id],[ProductCode],[Description],[Qty])
VALUES (@Payroll_Id,@ProductCode ,@Description,@Qty)
END

LongChalk · Answer 3 · 2019-04-02T07:06:53.710

Your update statement lacks a where statement. That is a major 'no-no', as it will (god forbid...) update all lines in the table.

Your insert statement lacks an identity insert, so consider the case where you are trying to update/insert id=5, but by now this line is deleted (not found in the where), and ids are much bigger. you would search for it -- > not find, and insert a new line (say id=101), then look for id=5 again, not find it again, and insert it again (say id=102), and so on... I don't think that's what you intended. Consider a Merge statement (when matched/when not matched) and get the best of both worlds. Also consider not deleting from the table, and instead add an 'IsDeleted' column (which allows 'reviving' a deleted row).

score 0 · Answer 4 · answered Apr 01 '19 at 10:40

Your SP does not meet the requirement of insert multiple records. It works only for a single record update or inserts, you have to pass multiple id's and values respectively for update multiple so use a different approach like XML as an input parameter so u can simply do this operation for multiple by extracting the XML data.

Insert and update multiple records via same stored procedure

4 Answers4