Temporary Variable In Procedure

[MJV57]

Is there a way to set a variable from a select query and then use it in a update query in the same procedure. I derive a value "avgwagerate" in a select statement which uses a caluculation to get this value. I now want this value put into another table using an update command. Can anyone help me.

 

[Bammy217]

MJV,

Try something like this

CREATE PROCEDURE sp_myProcedure
	@myID int
AS
	DECLARE @AWR FLOAT
BEGIN
	SET NOCOUNT ON;
	SET @AWR = (SELECT AVG(wagerate) FROM wages)

	UPDATE myTable
	SET avgWageRate = @AWR
	WHERE myId = @myID
	
    
END

if you are indeed talking about a procedure this is pretty much child's play

We never fail, we just find that the path to succes is never quite what we thought...

 

[markros]

Sure, you can use a variable as a result of select statement, e.g.

declare @AvgValue decimal(20,2)

select @AvgValue = avg([Value]) from myTable

Note, please don't start your SPs names from "sp_"

PluralSight Learning Library

 

[SQLSister]

And of course you never should use float if you are doing calculations either unless you like rounding errors.

"NOTHING is more important in a database than integrity." ESquared