CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Simran
Simran

Reputation: 569

Using calculated results of select statement to calculate another column in single statement

I have an SQL statement like this :

Insert into @Matches(RowId, PercentMatch) select distinct t.[Row],
cast(Max(Counts) as float)/(Abs(Max(Counts) - case when Max(VarLength) >= @tempint then Max(VarLength) else @tempint end) + Max(Counts))* 100 
As MatchingPercent from @Temp t Group by [Row] order by MatchingPercent desc

Next sql statement that I have is :

Update @Matches set Percentage =  PercentMatch * @constantVal

I would like to highlight that PercentMatch column is "calculated" in above Insert into select statement.

What I want to do : How I may merge it in one statement ?

Why I need this :

It is inside of a procedure that is executed by 20-30 stored procedures that are executed in asynch (using Service broker).

Upvotes: 0

Views: 28

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can place your SELECT in a subquery and insert Percentage value in the INSERT statement by performing the multiplication in an outer query:

Insert into @Matches(RowId, PercentMatch, Percentage) 
select tRow, MatchingPercent, MatchingPercent * @constantVal
from (
   select distinct t.[Row] as tRow, 
         cast(Max(Counts) as float) / (Abs(Max(Counts) 
         - 
         case 
           when Max(VarLength) >= @tempint then Max(VarLength) 
           else @tempint 
         end) + Max(Counts))* 100 As MatchingPercent 
from @Temp t 
Group by [Row] ) s

I also think order by clause is not of much use in a INSERT INTO SELECT statement.

Upvotes: 1

Related Questions