0

I have a procedure that accepts parameter @Data of type xml and updates an order in order table.

I have to call this procedure in a SSRS report for any order that need to be updatad and update the order table from one source table. Right now I have a CTE that lists all the orderIds that need to be updated in order table from this report.

lets say the CTE returns orderIds: 1234, and 4567.

The following two lines will update order 1234 in the order table.

    @XmlData XML = (select MyScalarValueFunction('1234'))
    execute UpdateProcedure @Data = @XmlData

and lets say the report data set query selects all requests from customers and if customer order has been processed, I want to update the orderId in the table.

    select c.CustomerName, c.OrderId from CustomerDemandsTable c

My challenge is: how can I incorporate the two line for updating the table in my report data set query for each order.

I have done updating tables from SSRS data set before. But over there each time I updated the whole table all at once and I did not have to declare any parameter and to call the update procedure in the middle of the query.

Any help is much appreciated.

Fatemeh Shakhsesalim
  • 51
  • 9

1 Answers1

0

If I understand your requirements correctly you want to perform 3 steps:

  1. Detect if data should be updated
  2. Update data
  3. Return data (including updated data)

To do this I would recommend building a stored procedure to handle these all these steps. Inside the stored procedure you can execute the query to pull the unprocessed records, then loop through each record and execute the update procedure, and finally query and return your fully updated dataset.

Jesse Potter
  • 827
  • 5
  • 20