Update multiple records using stored procedure

Question

Being a novice, I had a question that is helping me troubleshoot something I'm working on.

With the table created below, is there a way to modify the stored procedure to update multiple rows in the table

CREATE TABLE AccountTable
(
  RowID int IDENTITY(1, 1),
  AccountID varchar(2),
  AccountName varchar(50),
  SeqNum int,
  SeqDate datetime
)

CREATE PROCEDURE [ACCOUNTTABLE_UPDATE]
(
  @SeqNum int,
  @SeqDate datetime,
  @Account_ID varchar(2)
)

AS 
SET NOCOUNT ON
BEGIN
UPDATE AccountTable
SET SeqNum = @SeqNum, SeqDate = @SeqDate
WHERE AccountID = @AccountID
END

EXEC ACCOUNTTABLE_UPDATE  SeqNumValue, SeqDateValue, AccountIDValue

Running the stored procedure manually will of course edit one row, adding more values will lead to a too many arguments error. I just wanted to see if this stored procedure can in fact update more than one row in the table or if this should be modified to in fact handle providing more than the 3 parameters.

you can use table valued parameter or xml parameter or comma separated string to pass multiple values and use it. check this: https://sqlwithmanoj.com/2015/07/10/passing-multiple-values-with-a-single-parameter-in-a-stored-procedure-sql-server/ — Jatin Patel, Aug 19 '16 at 14:32
http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code — Ruslan K., Aug 19 '16 at 14:32

gofr1 · Accepted Answer · 2016-08-19T14:48:36.530

You can use Table-Valued Parameters (here is some info)

At first create table type and SP:

USE MyDB;  
GO  

-- Create a table type.  
CREATE TYPE SomeTableType AS TABLE ( 
    SeqNum int,
    SeqDate datetime,
    Account_ID varchar(2)
);  
GO  

-- Create a procedure to receive data for the table-valued parameter. 
CREATE PROCEDURE [ACCOUNTTABLE_UPDATE]  
    @T SomeTableType READONLY  
AS   
SET NOCOUNT ON  

UPDATE AT
SET SeqNum = t.SeqNum, SeqDate = t.SeqDate
FROM AccountTable AT
INNER JOIN @T t
    ON t.AccountID = AT.AccountID
GO

Then declare table with table type you created earlier, add data and run your SP:

-- Declare a variable that references the type. 
DECLARE @Temp AS SomeTableType;  

-- Add data to the table variable  
INSERT INTO @Temp VALUES
... 

-- Pass the table variable data to a stored procedure.
EXEC [ACCOUNTTABLE_UPDATE]  @Temp;  
GO

Another way is to pass your parameters as XML:

USE MyDB;  
GO  

CREATE PROCEDURE [ACCOUNTTABLE_UPDATE]  
    @x xml  
AS   
SET NOCOUNT ON  

UPDATE AT
SET SeqNum = t.v.value('@SeqNum','int'), 
    SeqDate = t.v.value('@SeqDate','datetime')
FROM AccountTable AT
INNER JOIN @x.nodes('/row') as t(v)
    ON t.AccountID = t.v.value('@AccountID','varchar(2)')

Then execute it:

DECLARE @x xml = N'<row SeqNum="1" SeqDate="2016-08-01 12:43:08.000" AccountID="AA"/>'

EXEC [ACCOUNTTABLE_UPDATE]  @x;

Just an update, the worst part is, I'm using SQL Server 2000 (I know) I just realized it doesn't work with that version. Although, still thanks for your help. — walangala, Aug 23 '16 at 14:25
Then you can use some permanent table to store values you need to update, and in SP: remove parameters (`@T SomeTableType READONLY `), change `INNER JOIN @T t` to `INNER JOIN YourNewTable t` and run it every time you need to update values in target table. — gofr1, Aug 23 '16 at 14:30

Update multiple records using stored procedure

1 Answers1