Iterate through table valued parameter and update the table having same id in table and table valued parameter

_{Friday, November 10, 2017 11:00 AM}

Iterate through table valued parameter and update the sql table having same id in table and table valued parameter

_{Friday, November 10, 2017 1:43 PM ✅Answered}

Solved using.

ALTER PROCEDURE [dbo].[sp_UpdateSalesItemStock]
       @tblItemStock SalesItemStock Readonly
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

;WITH StockCTE AS
( SELECT InvoiceMasterId,SUM(Quantity) as Quantity
  FROM @tblItemStock  
  group by InvoiceMasterId
)

UPDATE InvoiceMaster
SET
RemainingItems = (RemainingItems-STE.Quantity)
from InvoiceMaster IM
INNER JOIN StockCTE STE
on STE.InvoiceMasterId=   IM.InvoiceMasterId 
END

_{Friday, November 10, 2017 11:04 AM}

Is this an assignment question?

No table structure, no sample data, no modules where table valued parameter is defined

How do you expect someone to help you on this?

Seems like you copy pasted from some assignment/ online test without putting any effort to solve it by yourself!

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Friday, November 10, 2017 11:18 AM}

:)

Cool,

I tried my best, but lacking somewhere into logic. Why i haven't put any table schema or code..which will confuse my question and things i am doing.

So,if have sample code then fine or any guideline..Otherwise you can report abuse/delete

_{Friday, November 10, 2017 11:24 AM}

Without understand your schema, how can we suggest a solution which will fit in your scenario

We dont need to see your actual table/data

But atleast make a sample data with almost same structure with some sample data and then explain what do you want table valued parameter to be used for

In most cases what you would need would be to create a table variable of the same type and the  pass it for table valued parameter

And then use a join with table valued parameter to your table to do the update

like

UPDATE t
SET column= p.column
...
FROM yourtable t
JOIN tablevaluedparameter p
ON p.column = t.related column
...

an use case is here

https://visakhm.blogspot.ae/2010/02/aggregating-data-over-time-slots.html

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Friday, November 10, 2017 11:41 AM}

Iterate through table valued parameter and update the sql table having same id in table and table valued parameter

It's wrong from the first word. You don't iterate over tables (normally). You update all rows at once.

UPDATE tbl
SET    col1 = tv.col1,
       col2 = tv.col2,
       ...
FROM   tbl 
JOIN   @tv tv ON tbl.id = tv.id
       

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Friday, November 10, 2017 12:41 PM}

Its updating record only once. Following is the scenario:

1. While selling an item am adding same item more than once.

2. As suggested above, i am updating the the remaining items, but it is taking only first row from table parameter

What is going wrong

UPDATE InvoiceMaster
SET
RemainingItems = (RemainingItems-stock.Quantity)
from  @tblItemStock AS stock
JOIN InvoiceMaster IM
on stock.InvoiceMasterId=   IM.InvoiceMasterId 

_{Friday, November 10, 2017 12:50 PM | 1 vote}

You never said that there could be more than one row in the source that matches a single row in the target. And we don't read minds here.

The UPDATE FROM syntax is handy, but it is deceivable when one target row is matched by multiple source rows. The row is only updated once, but from which value is undefined.

In this particular case, you need to aggregate the results in the source per id. That is, replace @tblItemStock with the derived table

(SELECT InvoiceMasterId, SUM(Quantity) FROM @tblItemStock GROUP BY InvoiceMasterId) AS stock

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Friday, November 10, 2017 1:15 PM}

Sorry for not telling it at first.

Can we use looping in this scenario like .net?

_{Friday, November 10, 2017 1:56 PM}

Try the merge syntax:

ALTER PROCEDURE [dbo].[sp_UpdateSalesItemStock]
       @tblItemStock SalesItemStock Readonly
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
;WITH StockCTE AS
( SELECT InvoiceMasterId,SUM(Quantity) as Quantity
  FROM @tblItemStock  
  group by InvoiceMasterId
)

Merge  InvoiceMaster IM
Using StockCTE STE
on STE.InvoiceMasterId=   IM.InvoiceMasterId 
When matched then Update
SET 
RemainingItems = (RemainingItems-STE.Quantity);
 
END