Variable Math in Stored Procedure

[Arielle]

Okay here's what I'm trying to accomplish. I'm trying to create a stored procedure to make my life simple when it comes to a form I'm creating. One of the fields in it however is a calculation based on another form it's really simple.

I have the field, stampAvg, which is based on the current maximum of another column. The function to create the new stampAvg is as follows

SELECT MAX(stmScore)*1.25 AS stampAvg FROM tblStamp

That's it.

Now enter my stored procedure...

CREATE PROCEDURE spSetStamp
@name varchar(50)
@score float
@stamp float
AS
SET @stamp AS EXECUTE (SELECT MAX(stmScore)*1.25 AS stampAvg FROM tblStamp)
 INSERT INTO tblStamp (stmName,stmScore,stmStamp)
 VALUES(@name,@score,@stamp)
GO
 

However I'm quite certain that

SET @stamp AS EXECUTE (SELECT MAX(stmScore)*1.25 AS stampAvg FROM tblStamp)

Is incorrect

[cbellizzi]

Just a thought wouldn't you be better off using a function?