• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

VS SQL Code Generic Question

This a generic question for the gurus who write a lot of VS code around MS SQL.
I have these two scripts that return all the data necessary for transactions. The header contains the document header information. In this case, invoice number, date doc amount etc. The detail script will return at 2 least two records per header. One will be the credit and the other the debit amounts so the transaction will be balanced.

When designing the code, from a performance standpoint is it best to do as much of the processing directly in SQL? What I mean is I can write the code in VS and then pull one header record and its associated detail records and then call stored procedures and write the records to the database and then repeat for the next header record until the end of header records is reached or I can do all of this inside a stored procedure inside SQL. In our case we may be talking 500 header records and maybe 2000 detail records so the dataset is small.


-- THIS SCRIPT IS FOR THE PAYABLES HEADER INFORMATION

select distinct Number,case when DocumentType = 'Invoice' then 1 else 4 end as DocType,coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID, Purchases
from VendorBatchLoadTransaction
where wasimported = 0

-- THIS SCRIPT IS FOR THE PAYABLES DETAIL INFORMATION

select Number,case when DocumentType = 'Invoice' then 1 else 4 end as DocType,coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID, case when Debit >0 then 1 else 0 end as DBCR,Purchases, Debit, Credit,itemprice,ItemGLCombination,coalesce(itemtax,0) as itemtax
from VendorBatchLoadTransaction
where wasimported = 0
union all
select Number,case when DocumentType = 'Invoice' then 1 else 4 end as DocType,coalesce(Reference,'') as Reference,coalesce(BatchName,'') as BatchName,PurchaseDate,PostDate,coalesce(CRI,'') as PONUMBER,VENDORID,case when sum(itemprice)<0 then 1 else 0 end as DBCR,Purchases,case when sum(itemprice)<0 then abs(sum(itemprice))+abs(sum(itemtax)) else 0 end as Debit,case when sum(itemprice)<0 then 0 else abs(sum(itemprice))+abs(sum(itemtax)) end as Credit,sum(itemprice),'GN-2115-000-000-0-00000',coalesce(sum(itemtax),0) as itemtax
from VendorBatchLoadTransaction
where wasimported = 0
group by Number,DocumentType,Reference,BatchName,PurchaseDate,PostDate,CRI,VENDORID,Purchases
order by Number, Purchases
0
rwheeler23
Asked:
rwheeler23
1 Solution
 
WikkardCommented:
The answer depends on a couple of factors such as:
You TSQL skill level.
Your C# skill level.
Ease of maintenance

I suggest that you should prepare all the data in one go into some staging tables in SQL server.

Then when you have run all the calculations and the data is ready, you run your  reports (invoices). I'm guessing that the reporting is done with VS or SSRS ?
0
 
rwheeler23Author Commented:
I am actually transferring records from an external application to an ERP solution. The data has been scrubbed and now has to be committed to the ERP solution. Given that the ERP solution has some very stringent rules I think I am going to use a combination of VS C# and stored procedures. I will need to display messages to the user if certain fields have values out of range. If it was just a straight dump I would use all SQL.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now