C# and SQL Server: updating trouble with stored procedures

Question

Using:

Microsoft SQL Server 2008
Microsoft Visual Studio 2010
C#
.NET 4.0
WinForms

Ok this is my stored procedure. Brace yourself, it's rushed because I have a deadline of 24 hrs and I was told yesterday as I was leaving work (yes I was very annoyed).

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MyPareto]
    @pgParam varchar(255)
AS
   SELECT i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sales6months,
   a.LostSales6Months,
   dbo.NewParetoAnalysis.Pareto

FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iLines.Part,
                            dbo.iLines.Pg,
                            SUM(dbo.iLines.Qty) as sales6months,
                            dbo.iLines.Prefix 
                     FROM Autopart.dbo.iLines 
                     where prefix = ''i''
                     and [datetime] > dateadd(month, -6, getdate())
                     group by 
                     dbo.ilines.pg,
                     dbo.ilines.part,
                     dbo.ilines.prefix
                     order by sales6months desc') i
RIGHT JOIN
dbo.OldParetoAnalysis
on
i.part collate SQL_Latin1_General_CP1_CI_AS = dbo.OldParetoAnalysis.Part
INNER JOIN
dbo.NewParetoAnalysis
ON
dbo.OldParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS =    dbo.NewParetoAnalysis.Part

LEFT JOIN
OPENQUERY(SACBAUTO, 'SELECT dbo.aLines.Part,
                            dbo.aLines.Pg,
                            SUM(dbo.aLines.Qty) as LostSales6Months,
                            dbo.aLines.Prefix 
                     FROM Autopart.dbo.aLines 
                     where prefix = ''d''
                     and [datetime] > dateadd(month, -6, getdate())
                     group by 
                     dbo.alines.pg,
                     dbo.alines.part,
                     dbo.alines.prefix
                     order by LostSales6Months desc') a
ON
dbo.NewParetoAnalysis.Part collate SQL_Latin1_General_CP1_CI_AS = a.part
/*FULL OUTER JOIN
dbo.NewParetoAnalysis
ON
a.part collate SQL_Latin1_General_CP1_CI_AS = dbo.NewParetoAnalysis.Part*/
WHERE
i.pg = @pgParam
GROUP BY
   i.pg,
   dbo.OldParetoAnalysis.Pareto,
   i.part,
   i.sales6months,
   a.LostSales6Months,
   dbo.NewParetoAnalysis.Pareto
ORDER BY
dbo.OldParetoAnalysis.Pareto asc

Procedure works great, pretty fast too (no idea how hehe). The problem I now have is how to update.

I only want to update 2 tables: OldPareto and NewPareto.

The data will only come from one column which is the NewPareto column. This will update the column in both tables.

Normally its just a simple UPDATE call in the code using the SqlDataAdapter and DataSet.

But since I have this crazy stored procedure I'm troubled as to how this works.

Any C# code you need let me know.

Many thanks!

Nikola Markovinović · Accepted Answer

A long winded way would be too get the new pareto into a new table in my code, then use the table to update my 2 sql tables

Or in sql procedure itself. I still do not understand what are your objects and their relationships, but here is general way to perform similar updates:

First declare temporary table at the beginning of procedure:

declare @tmpTable table 
(
  pg int, 
  oldPareto int, 
  part int, 
  sales6months int, 
  LostSales6Months int,
  newPareto int
)

obviously, column types do not match yours so you will need to type them properly. Perform insert into using your select:

insert into @tmpTable 
     (pg, oldPareto, part, sales6months, LostSales6Months, newPareto)
     select ...

And then perform updates:

update oldPareto
   set oldPareto = a.oldPareto,
       newPareto = a.newPareto
  from oldPareto
    inner join @tmpTable a
       on ... -- However you join oldPareto table with results of query

update newPareto
   set oldPareto = a.oldPareto,
       newPareto = a.newPareto
  from newPareto
    inner join @tmpTable a
       on ... -- However you join newPareto table with results of query

C# and SQL Server: updating trouble with stored procedures

Answers (2)

Related Questions