CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Koosh
Koosh

Reputation: 896

Creating a stored procedure that would let me do calculations and insert values into tempTable

I have started working on a stored procedure to deal with some calculations I need to do. The gist of it is this:

Create Table #Temp 
(
    ClientName varchar,
    ClientAddress varchar,
    Units1 int,
    Units2 int,
    UnitsUsed int,
    UnitsSpec int
)

Insert into #Temp (ClientName, ClientAddress, Units1, Units2)
    Select 
        x.ClientName, x.ClientAddress, y.Units1, y.Units2 
    From
        tblClients x 
    Left Join 
        tblClientUnits y On x.clientId = y.clientid

The select statement is a bit more complicated, I'm trying to simplify it for readability purposes.

Everything up to here works, so I create a temp table, insert all the info, what I'm trying to do now is for these last 2 columns in the #Temp table, I need to do some calculations, so I'm trying to somehow re-insert into the temp table UnitsUsed which would be total of Units1 + Units2, and do the same thing for UnitsSpecwhich would be Units1 + Units2 / 3

What would be a good way to do this? Is this doable?

Edit sample data - desired end result:

ClientName     ClientAddress    Units1     Units2    UnitsUsed    UnitsSpec
---------------------------------------------------------------------------
Bob S          11 Jude Lane     10         20        30           10

Upvotes: 0

Views: 390

Answers (3)

Red
Red

Reputation: 3267

You can do it at the same time as the other insert

Create Table #Temp (
    ClientName varchar,
    ClientAddress varchar,
    Units1 int,
    Units2 int,
    UnitsUsed int,
    UnitsSpec int
)

Insert into #Temp (
    ClientName,
    ClientAddress,
    Units1,
    Units2,
    UnitsUsed,
    UnitsSpec
)
Select x.ClientName
    , x.ClientAddress
    , y.Units1
    , y.Units2 
    , (y.Units1 + y.Units2)
    , (y.Units1 + y.Units2) /3
from tblClients x 
    left join tblClientUnits y on x.clientId = y.clientid

Upvotes: 1

Paurian
Paurian

Reputation: 1402

After your INSERT statement, run the UPDATE statement like this:

UPDATE #Temp SET UnitsUsed = Units1 + Units2, UnitsSpec = Units1 + (Units2 / 3);

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Replace the word "re-insert" with the word "update" in your thinking.

Or just do the calculations in your initial INSERT..SELECT.

Upvotes: 0

Related Questions