MSSQL update multiple records all at once by one stored procedure

Question

I have a stored procedure that currently looks like this:

create procedure zsp_updateTyp5Graph
(
    @DayList nvarchar(1000),
    @Sales int,
    @SearchedUserId int
)
as 
update SearchedUserGraphData
    set SalesForDay=@Sales
where Day in (SELECT * FROM dbo.SplitString(@DayList)) and SearchedUserId=@SearchedUserId

The daylist parameter looks like following:

0,1,2,3,4,5,6

I have in my SearchedUserGraphData table two columns and 7 records(7 days) that should be updated. For example:

   Day    Sales
    0       5
    1       6
    2       4
    3       3
    4       7
    5       9 
    6      11

I have "partially" solved this by passing list of days... But I'm unable to find out how can I pair up this @DayList parameter with sales data...

I have a SplitString function that I've created and looks like this for matching the records in DB:

ALTER FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX) )
RETURNS
    @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit)  
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @returnList 
        SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

    INSERT INTO @returnList
    SELECT @stringToSplit

    RETURN
END

Now the issue is that I need to somehow pass from my C# application combined list of Days and their corresponding sales, and then update the records all at once, without calling the procedure 7 times to update 7 records (that would be catastrophical from performance standpoint in my eyes)...

Can someone help me out with this?

P.S. Guys, the "best" idea that I get on this one is:

The daylist parameter looks like following:

    0,1,2,3,4,5,6

And then sales:

    11,22,44,55,66,77,88

This is the way I can pass the sales and days parameters... But then what? I cannot know what sale corresponds to which day...

I could form a key-value pair dictionary string in my C# application maybe like:

{ Day: 1 Sales: 44, Day: 2 Sales: 55 }

this is how I could form the string.. But then I need to break down this string in MSSQL to match => day 1 => update with sales 44 ...

@hardkoded yeah but I need to somehow match sales for days... Im not sure how to do that ;/ — User987, Aug 31 '18 at 13:25
@hardkoded see my updated question , can we do something like that in mssql ? — User987, Aug 31 '18 at 13:28

hardkoded · Accepted Answer · 2018-08-31T13:48:37.027

2

You can create a SalesPerDay type

CREATE TYPE [dbo].[SalesPerDay] AS TABLE (
    day INT NOT NULL,
    sales INT NOT NULL
);

Then, your proc would look like this

create procedure zsp_updateTyp5Graph
(
@DayList SalesPerDay READONLY,
@SearchedUserId int
)
as 
update SearchedUserGraphData
set SalesForDay=dayList.sales
FROM SearchedUserGraphData baseTable
   INNER JOIN @DayList dayList ON dayList.day = baseTable.day
WHERE SearchedUserId=@SearchedUserId

And from the C#, you can build a DataTable and send it to the proc.

var table = new DataTable();
table.Columns.Add("day", typeof(int));
table.Columns.Add("sales", typeof(int));

//Add data
table.Rows.Add(new []{1, 200});
table.Rows.Add(new []{2, 200});

//More code
Command.Parameters.AddWithValue("@DayList", table);

edited Aug 31 '18 at 13:48

answered Aug 31 '18 at 13:33

hardkoded

18,915
3
52
64

Hey btw. could you include the solution with passing the datatable as well ? Just a sample one so I can speed up ther process? I haven't worked with passing datatables as parameters before... I feel like I'll be making a tonne of mistakes while doing this .. – User987 Aug 31 '18 at 13:44
hardkoded, when trying to alter the procedure with the one that you provided above I get an error: Msg 137, Level 15, State 2, Procedure zsp_updateTyp5Graph, Line 8 [Batch Start Line 0] Must declare the scalar variable "@Sales". – User987 Aug 31 '18 at 13:47
Done, and fixes the sales stuff – hardkoded Aug 31 '18 at 13:49
hey btw... I have tried calling the procedure like this: using(var ctx = new context() { ctx.zsp_updateTyp5Graph() }; the only visible parameter here is searcedduserid... It doesn't allows me to pass the datatable parameter? :/ – User987 Aug 31 '18 at 13:52
I was hoping for that... It seems easier than doing a custom sql command C# code , to just simply call the procedure.. but it doesn't allows to pass the datatable parameter.. It doesn't sees it as a parameter at all :/ – User987 Aug 31 '18 at 13:54
hardkoded - worst case scenario - how would this implementation - calling of procedure look like with a custom sqlcommand ? – User987 Aug 31 '18 at 13:57
There are many examples around https://stackoverflow.com/a/1260998/2373249 – hardkoded Aug 31 '18 at 14:07

MSSQL update multiple records all at once by one stored procedure

1 Answers1