Hi! New to SQL and need to know if this can be done. I need a stored procedure that will perform a select statement and then use the result in an update statement just after that. All within the same stored procedure if possible. Here is what I have so far--
CREATE PROCEDURE spGetChargesOld
@unit_id INT
AS
BEGIN
SELECT (level1*(select repair_cost from RepairOptions where repair_desc = 'level1')
+ HBreplace*(select repair_cost from RepairOptions where repair_desc = 'S9HB')
+ controlcard*(select repair_cost from RepairOptions where repair_desc = 'S9CC')
+ fan*(select repair_cost from RepairOptions where repair_desc = 'S9Fan')
+ consolidation*(select repair_cost from RepairOptions where repair_desc = 'consolidation')
+ psu_type*(select repair_cost from RepairOptions where repair_desc = 'OldGenPSU'))
as *unit_total*
from RepairDetails
UPDATE Units
SET unit_total = *unit_total*
END
You have a whole lot going on here. First, for Repair Details, are you updating the entire table or just where the Unit_Id = parameter? Same with Units? Can you provide DDL and sample data? Also, joining on Description and having 'descriptions' as columns isn't a good idea. Table should be something like:
The only column I want to UPDATE is the unit_total column within the Units table where unit_id = @unit_id. I am using the rest of the SELECT statement to multiply two columns, the repair_cost column with the various repair detail columns that are in the RepairDetails table.
I'd like to take the resulting product of each multiplication and add them together, then put that amount into the unit_total column of the Units table.
I get what you want to do, but I don't know what the other tables look like or have in them. If you want to simply run an update, then you can use below, but I don't know how the results will look
Update U
set Unit_Total = (level1*(select repair_cost from RepairOptions where repair_desc = 'level1') + HBreplace*(select repair_cost from RepairOptions where repair_desc = 'S9HB') + controlcard*(select repair_cost from RepairOptions where repair_desc = 'S9CC') + fan*(select repair_cost from RepairOptions where repair_desc = 'S9Fan') + consolidation*(select repair_cost from RepairOptions where repair_desc = 'consolidation') + psu_type*(select repair_cost from RepairOptions where repair_desc = 'OldGenPSU'))
from RepairDetails r
join Units u
on U.Unit_ID = @Unit_ID
I want to take the info from RepairDetails, multiply by the appropriate repair option cost, then input that product into Units where the unit_id and order_id match the unit_id and order_id in the RepairDetails table.
I've been rather busy but have had time to work more on this --
After updating my stored procedure as you stated here, it doesn't appear the Update is working. The procedure executes as far as I can tell, I get no error. But the value of the Units.unit_total column remains NULL. Here is my stored procedure currently:
@unit_id INT,
@order_id INT,
@PSUtype nvarchar(50)
AS
BEGIN
Update U
set unit_total = (level1*(select repair_cost from RepairOptions where repair_desc = 'level1')
+ HBreplace*(select repair_cost from RepairOptions where repair_desc = 'NewGenHB')
+ basicHB*(select repair_cost from RepairOptions where repair_desc = 'BasicHB')
+ advHB*(select repair_cost from RepairOptions where repair_desc = 'AdvHB')
+ controlcard*(select repair_cost from RepairOptions where repair_desc = 'NewGenCC')
+ fan*(select repair_cost from RepairOptions where repair_desc = 'NewGenFan')
+ consolidation*(select repair_cost from RepairOptions where repair_desc = 'consolidation')
+ (select repair_cost from RepairOptions where hw_key = @PSUtype))
from RepairDetails r
join Units u
on U.Unit_ID = @Unit_ID
END
You've added more parameters. Does the below return what you are expecting the updated value to be?
-- Fill in parameters accordingly
declare
@unit_id INT,
@order_id INT,
@PSUtype nvarchar(50)
select unit_total = (level1*(select repair_cost from RepairOptions where repair_desc = 'level1')
+ HBreplace*(select repair_cost from RepairOptions where repair_desc = 'NewGenHB')
+ basicHB*(select repair_cost from RepairOptions where repair_desc = 'BasicHB')
+ advHB*(select repair_cost from RepairOptions where repair_desc = 'AdvHB')
+ controlcard*(select repair_cost from RepairOptions where repair_desc = 'NewGenCC')
+ fan*(select repair_cost from RepairOptions where repair_desc = 'NewGenFan')
+ consolidation*(select repair_cost from RepairOptions where repair_desc = 'consolidation')
+ (select repair_cost from RepairOptions where hw_key = @PSUtype))
from RepairDetails r
join Units u
on U.Unit_ID = @Unit_ID
This actually returns 4 rows of data, which confuses me because I would have expected one result. Here is the query I ran, not exactly what you had but I think basically is the same?
select unit_total = (level1*(select repair_cost from RepairOptions where repair_desc = 'level1')
+ HBreplace*(select repair_cost from RepairOptions where repair_desc = 'NewGenHB')
+ basicHB*(select repair_cost from RepairOptions where repair_desc = 'BasicHB')
+ advHB*(select repair_cost from RepairOptions where repair_desc = 'AdvHB')
+ controlcard*(select repair_cost from RepairOptions where repair_desc = 'NewGenCC')
+ fan*(select repair_cost from RepairOptions where repair_desc = 'NewGenFan')
+ consolidation*(select repair_cost from RepairOptions where repair_desc = 'consolidation')
+ (select repair_cost from RepairOptions where hw_key = 2))
from RepairDetails r
join Units u
on U.Unit_ID = 6
The value of @PSUtype has not been defined - therefore it is a NULL value.
+ (select repair_cost from RepairOptions where hw_key = @PSUtype)
This will not find a value - and therefore returns a NULL value. Anything plus NULL will be NULL...
To make this easier to work with - I would recommend first PIVOTing the repair costs. To do that correctly though, we would need to see all of the columns in the RepairOptions table and how you want to identify the correct set of costs to be associated with this request.
It appears you now have a new column in the table named hw_key that isn't used anywhere else in your query. How is that column defined in the table and populated?
Pivoting the repair costs would then simplify the update statement. Again - would need to see the full table definitions and how you identify which repair costs to associate with the specific repair details.
The hw_key column is used to differentiate pricing between a couple different models of 'NewGenPSU' and in cases where no PSU is used, this column will be blank in the RepairDetails tbl. The RepairDetails info is basically a 'quantity' record and then I need the 'quantity' multiplied by the cost.
One thing I want to mention althought I'm not sure this is related, a couple of my columns are BIT columns, for example NewGenCC is a BIT column, I assumed for calculation purposes it would see the column as a 0 or 1, maybe that is wrong?
The reason for 4 rows returned is the join from RepairDetails to Units.
from RepairDetails r
join Units u
on u.Unit_id = r.Unit_ID
where U.Unit_ID = 6
Again, we're all guessing unless you provide DDL and sample data. We don't know what your structures and data look like and screenshots only help a little
Help us help you. provide the sample data as follows
if object_Id('tempdb..#Units') is not null
drop table #Units;
create table #Units(unit_id int, order_id int, unit_total int)
insert into #units
select 6,1, 9
And do this for all of the tables needed to help resolve your question.
The other option is for you to give us direct access to your SQL Server
This does not appear to be the full table - are there other columns that you are excluding? More rows in the table? How do you determine which repair costs to associate with a specific repair detail?
It seems that the repair details table is pivoted already - how does that occur?
In your latest sample - I don't see the same values that are listed in your query, which also happen to be different than the first query you posted. At this point it is impossible to determine what columns you need to return from RepairOptions given a specific row in RepairDetails. I have to assume that the RepairDetails table has many more columns...or that is is dynamically created for each row - but that is an assumption I am not real sure is correct.
CREATE PROCEDURE [spGetChargesOld]
@unit_id INT
AS
BEGIN
UPDATE
[a]
SET
[a].[unit_total] = [b].[sum_repair_cost]
FROM
( SELECT
*
FROM
[RepairDetails]
WHERE
[unit_id] = @unit_id) AS [a]
JOIN
( SELECT
SUM([repair_cost]) AS [sum_repair_cost]
FROM
[RepairOptions]
WHERE
[repair_desc] IN ( 'level1', 'S9HB', 'S9CC', 'S9Fan', 'consolidation', 'OldGenPSU' )
AND [unit_id] = @unit_id) AS [b]
ON [a].[unit_id] = [b].[unit_id];
END;
