sql - Optimization of a Stored Procedure -

i have stored procedure this:

select * table_1 t1 join table_2 t2 on t1.t2id = t2.id t1.id >= 1000    , t2.name '%a%' --and lot of others offset 20 rows fetch next 20 rows  --from , similar previous query  select @total = count(*), @sum = sum(t2.some_field) table_1 t1 join table_2 t2 on t1.t2id = t2.id  t1.id >= 1000 , t2.name '%a%' --and lot of others' 

i'm interested in "clean" variant of writing such stored procedure. want separate similar code in 1 place avoid errors when forget copy-paste section

now i'm thinking temporary table. maybe have better solution?

thanks!

since it's 2 step processing, need run query twice. can store data #temporary table, or @variable table. trick here minimize data store, use offset ... fetch next limit data you're going store in temp or variable table.

one way re-write query is:

    select *, sum(totalamount) on () somesum, count(1) on () totalrows        #tmp        table_1 t1             join table_2 t2 on t1.t2id = t2.id     t1.id >= 1000 , t2.name '%a%' --and lot of others     order 1 --<<<<<< mandatory     offset 20 rows     fetch next 20 rows      select  top 1             @sum    = somesum,             @total  = totalrows        #tmp      select  * --<< except somesum, totalrows columns        #tmp 

just note order by mandatory offset ... fetch next. also, sum , count still work on entire result set, if results limited offset ... fetch next.

hope makes sense.