Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Basic math in MS SQL stored procedure across three tables

Posted on 2010-08-24
Medium Priority
1,011 Views
I'm trying to do some very basic math in MS SQL by using one stored procedure to SUM the value of a column in Table_A, then divide the SUM from Table_A by the page view total from Table_B - and UPDATE the value in Table_C.

Basically I'm trying to work out the eCPC for a lead generating site on a page view basis (so every time a user visits a profile the eCPC is updated against the SUM of leads generated).

val = common denominator across all three tables

So step 1 is:

Step 2:
SELECT page_views FROM Table_B WHERE camp_id='val'

Step 3:

Step 4:
UPDATE Table_C SET lead_eCPC='new_eCPC' WHERE camp_id='val'

Now I can easilly do this in ASP, but I have to execute an SQL script three times to do this - which I believe is terribily inefficient as I want it to happen each time a profile is viewed.

Can someone please help we write a stored procedure for this so I only have to execute it once on each profile view?

Many Thanks!

C
0
Question by:BestAviation
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 75

Expert Comment

ID: 33510284
Something like this perhaps:
``````U PDATE  c
FROM    Table_C c
FROM    Table_A
WHERE   camp_id = 'val'
) a ON c.camp_id = a.camp_id
INNER JOIN Table_B b ON c.camp_id = b.camp_id
``````
0

LVL 22

Assisted Solution

Om Prakash earned 600 total points
ID: 33510298
try
``````Create procedure yourProc (@camp_id varchar(20))
As
Begin
declare @new_eCPC int
SELECT @new_eCPC = @lead_sum / page_views  FROM Table_B WHERE camp_id=@camp_id
UPDATE Table_C SET lead_eCPC= @new_eCPC WHERE camp_id=@camp_id
end
``````
0

LVL 4

Assisted Solution

udaydidigam earned 600 total points
ID: 33510377

Create procedure Proc_name (@camp_id varchar(50))
As
declare @sum_A int
declare @div
Begin
SELECT @div = @sum_A /SUM(page_views)  FROM Table_B WHERE camp_id=@camp_id
UPDATE Table_C SET lead_eCPC= @div WHERE camp_id=@camp_id
end

0

LVL 5

Accepted Solution

DerZauberer earned 800 total points
ID: 33510468
Well i cannot see what datatypes your fields have, but should be someting like the following.

You can execute it like this: EXEC UpdateECPC 'campid'

``````CREATE PROCEDURE UpdateECPC @camp_id varchar(20)
AS
BEGIN
SET NOCOUNT ON
DECLARE @page_views int
SET @page_views = (SELECT page_views FROM Table_B WHERE camp_id = @camp_id)
UPDATE Table_C SET lead_eCPC = @lead_sum / @page_views WHERE camp_id = @camp_id
SET NOCOUNT OFF
END
``````
0

Author Comment

ID: 33511260
That is brilliant! Thanks very much guys - I don't use stored procedures very often so wasn't aware you could declare variables that easilly and execute multiple lines.
0

LVL 75

Expert Comment

ID: 33517187
Strange choice.  Multiple SELECT statements over a single UPDATE statement?  Obviously performance is not important to you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll