what is the best practice for having a select query, sum query, etc in an insert query?

Question

0.00/5 (No votes)

See more:

SQL

insert into revenue (
   Year,
   Newsales,
   Beginninginventory,
   Purchases,
   Goodforsales,
   Endinventory,
   Costofsales,
   GrossIncome,
   Salesandwages,
   sss,
   Rental,
   Postage,
   Lightandwater,
   Freight,
   Fuel,
   Officesupplies,
   Taxesandlicenses,
   Representation,
   Depreciation,
   Transportation,
   Repairs,
   Miscellaneous,
   Totaloperatingexpense,
   Provisions,
   Netincomefinal
)
VALUES (
   @one,
   (Select SUM(TotalPrice) FROM inventoryout where revenue.Newsales=@two),
   @three,
   @four,
   @five,
   @six,
   @seven,
   (select NewSales - Costofsales from revenue where GrossIncome=@eight),
   @nine,
   @ten,
   @eleven,
   @twelve,
   @thirteen,
   @fourteen,
   @fifteen,
   @sixteen,
   @seventeen,
   @eighteen,
   @nineteen,
   @twenty,
   @twentyone,
   @twentytwo,
   @twentythree,
   (select GrossIncome - Totaloperatingexpense from revenue where Netincomebeforetaxes = @twentyfour),
   @twentyfive,
   (Select Netincomebeforetaxes - Provisions from revenue where Netincomefinal = @twentysix)
)

Posted 8-Mar-15 5:54am

_bluRe_

Updated 8-Mar-15 6:01am

phil.o

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2015-03-08T06:07:00

Solution 1

Maybe you can try to compute the subqueries first, and store them in other variables, that you can then include in your main query.
Something like:

SQL

DECLARE @computedTwo money = SELECT SUM(TotalPrice) FROM inventoryout WHERE revenue.Newsales=@two
-- Then
INSERT INTO revenues
-- ...
VALUES (
-- ...
@computedTwo
-- ...
)

Do you catch the idea?

Posted 8-Mar-15 6:07am

phil.o

Comments

_bluRe_ 8-Mar-15 12:12pm

hmm something like execute the sub query and assign to variable then put them into the main query, right?

phil.o 8-Mar-15 12:17pm

Yes. That is not necessarily optimized, though, but that is the only thing I can say, given the fact that we don't know much of the structure of revenues and revenue tables, and how they are related. It has the advantadge to be much easier to read and debug.

_bluRe_ 8-Mar-15 12:15pm

how can i code this in buttonevent in c#? like assign in a string variable, then..?

phil.o 8-Mar-15 12:18pm

You can, but that does not mean you should. I do not like to put implementations in event handlers; I prefer having implementations in meaningfully-named methods, which I call from my event handlers. But that's a question of choice.

_bluRe_ 8-Mar-15 12:24pm

cmd.commandText = "insert into ....values(@computedTwo)..";
ComputedTwo();
cmd.Parameter.AddwithValue(@computerTwo);

something like this? thats what i want to do but i dont know how to put the method in the parameter..

phil.o 8-Mar-15 12:35pm

No. Rather something like:

Public Function GetComputedTwo(ByVal two As ???) As Decimal
'' Here the query to get the value of @computedTwo from the @two parameter
End Function

And then:

cmd.Parameters.AddWithValue(@computedTwo, GetComputedTwo(two))

_bluRe_ 9-Mar-15 1:37am

i tried it but still its wrong. hmm

Vasid Qureshi · Answer 2 · 2015-03-08T06:08:00

i use store procedure to handle them at once

create procedure testtable
(
@colum1 int,
@colum2 nvarchar(50),
@colum3 nvarchar(50),
@colum4 nvarchar(50),
@flag int,
)
as
BEGIN
	if (@flag=0)
	BEGIN
	-- select query
	END
	
	if (@flag=1)
    BEGIN
		-- insert query
    END
    
    if (@flag=2)
    BEGIN
		-- update query
    END
    
    if (@flag=3)
    BEGIN
		-- delete query
    END
END