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.

Answer 1

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;