I have following table structure

table1

(col1 int, col2 int, col3 int)

and i have procedure as which returns sum as follows

Create procedure test

as

begin

Declare @col1total

Declare @col2total

Declare @col3total

Select @col1total = Sum(col1)

From table1

Select @col2total = Sum(col2)

From table1

Select @col3total = Sum(col3)

From table1

End

Is it possible to use a single dynamic query to assign values

while trying

Declare @counter int

Declare @dynqry nvarchar(max)

Select @counter = 1

While @counter<=3

Select @dynqry = 'SELECT @col'+CONVERT(varchar(5),@counter) +'=SUM('+CONVERT(varchar(5),@counter)'+') From table1'

exec sp_executesql @dynqry

am getting error must declare variable @col1

Is this acheivable?