dynamic SQL (column name variable) with aggregate functions

Question

I have the following columns in a table: IdRec dataora aparat1 aparat2 .... aparat100 and the following code in a stored procedure:

ALTER PROCEDURE [dbo].[GetConsumRealElQRo] 
    @id_aparat int,
    @datastart nvarchar(20),
    @datastop nvarchar(20)
AS

declare
@val_max real,
@data_min datetime,
@data_max datetime,
@val_data_min real,
@val_data_max real,
@consum real

BEGIN
    SET NOCOUNT ON;

if @id_aparat=1
begin
    select @val_max=max(aparat1),@data_min=min(dataora),@data_max=max(dataora) from table_name where (dataora between convert(datetime,@datastart,101) and convert(datetime,@datastop,101)) and aparat1 is not null

    set @val_data_min=(
    select aparat1 from table_name
    where dataora=@data_min)

    set @val_data_max=(
    select aparat1 from table_name
    where dataora=@data_max)

    if @val_data_max<>@val_max
    begin
    set @consum=@val_max-@val_data_min+@val_data_max
    end
    else
    begin
    set @consum=@val_data_max-@val_data_min
    end

    select @consum
end 

if @id_aparat=2
begin
    select @val_max=max(aparat2),@data_min=min(dataora),@data_max=max(dataora) from table_name where (dataora between convert(datetime,@datastart,101) and convert(datetime,@datastop,101)) and aparat2 is not null

    set @val_data_min=(
    select aparat2 from table_name
    where dataora=@data_min)

    set @val_data_max=(
    select aparat2 from table_name
    where dataora=@data_max)

    if @val_data_max<>@val_max
    begin
    set @consum=@val_max-@val_data_min+@val_data_max
    end
    else
    begin
    set @consum=@val_data_max-@val_data_min
    end

    select @consum
end 

and so on, for each of those 100 columns named aparat1, aparat2... aparat100.

So the procedure receives as parameters an integer column identifier and two dates, do some calculations and returns a value. The column identifier identifies the column and gives the column name (which is "aparat" + @id_aparat). All I want is to to put the calculations block in a for statement, instead of copying it a hundred times, for each value of @id_aparat. Cam this be done using dynamic SQL?

EDIT: WORKING CODE Problem solved. For anyone interested, here is the working code:

USE [2013date]
GO
/****** Object:  StoredProcedure [dbo].[GetConsumRealElQRo]    Script Date: 4/18/2013 2:39:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetConsumRealElQRo] 
    @id_aparat int,
    @datastart nvarchar(20),
    @datastop nvarchar(20)
AS

declare
@val_max real,
@data_min datetime,
@data_max datetime,
@val_data_min real,
@val_data_max real,
@consum real

BEGIN
    SET NOCOUNT ON;

declare 
@aparat as nvarchar(10),
@tabel as nvarchar(50),
@query as nvarchar(1000)

set @aparat = 'aparat' + CONVERT(nvarchar(3),@id_aparat)
set @tabel = 'table_name'

set @query = 'select @val_max=max(' + @aparat + '),@data_min=min(dataora),@data_max=max(dataora) from ' + @tabel + '
    where (dataora between convert(datetime,''' + @datastart + ''',101) and convert(datetime,''' + @datastop + ''',101)) and ' + @aparat + ' is not null'
--select @query
exec sp_executesql @query, N'@val_max real output, @data_min datetime output, @data_max datetime output', @val_max output, @data_min output, @data_max output
--select @val_max
--select @data_min
--select @data_max

set @query = 'set @val_data_min=(select ' + @aparat + ' from ' + @tabel + ' where dataora=''' + Convert(nvarchar(30),@data_min,121) + ''')'
--select @query
exec sp_executesql @query, N'@val_data_min real output',  @val_data_min output
--select @val_data_min

set @query = 'set @val_data_max=(select ' + @aparat + ' from ' + @tabel + ' where dataora=''' + Convert(nvarchar(30),@data_max,121) + ''')'
--select @query
exec sp_executesql @query, N'@val_data_max real output',  @val_data_max output
--select @val_data_max

if @val_data_max<>@val_max
    begin
        set @consum=@val_max-@val_data_min+@val_data_max
    end
else
    begin
        set @consum=@val_data_max-@val_data_min
    end

select @consum

END

Answer 1

Please try with following coding....

    ALTER PROCEDURE [dbo].[GetConsumRealElQRo] 
    @id_aparat int,
    @datastart nvarchar(20),
    @datastop nvarchar(20)
AS

declare
@val_max real,
@data_min datetime,
@data_max datetime,
@val_data_min real,
@val_data_max real,
@consum real

BEGIN

    SET NOCOUNT ON;

    Declare @ExecutionString NVARCHAR(500)

    Set @ExecutionString  = '
    Select @val_max=max(aparat'+ Cast( @id_aparat  AS Varchar) +'),@data_min=min(dataora),@data_max=max(dataora) from table_name where (dataora between convert(datetime,@datastart,101) and convert(datetime,@datastop,101)) and aparat1 is not null

        set @val_data_min=(
        select aparat'+ Cast( @id_aparat  AS Varchar) +' from table_name
        where dataora=@data_min)

        set @val_data_max=(
        select aparat'+ Cast( @id_aparat  AS Varchar) +' from table_name
        where dataora=@data_max)

        if @val_data_max<>@val_max
        begin
            set @consum=@val_max-@val_data_min+@val_data_max
        end
        else
        begin
            set @consum=@val_data_max-@val_data_min
        end

        Select @consum
    '

    Exec SP_Executesql @ExecutionString, N'@id_aparat INT', @id_aparat

    SET NOCOUNT OFF;
Go  

Answer 2

I have not had the time to try this query, but the answer to your question is yes. You can declare an nvarchar variable, @sql1, set this variable to be a string of your query and then execute this variable. So, again with no testing of this query, here we go. The SELECT at the end is to be able to see the query you are about to execute, and exec(@sql1) runs the query. Hopefully this will make sense and help you out!

declare @sql1 nvarchar(4000)
set @sql1 = '
select @val_max=max(aparat' + convert(nvarchar(10),@id_aparat) + '),@data_min=min(dataora),@data_max=max(dataora) from table_name where (dataora between convert(datetime,@datastart,101) and convert(datetime,@datastop,101)) and aparat' +convert(nvarchar(10),@id_aparat)+' is not null

    set @val_data_min=(
    select aparat' + convert(nvarchar(10),@id_aparat) + ' from table_name
    where dataora=@data_min)

    set @val_data_max=(
    select aparat' + convert(nvarchar(10),@id_aparat) + ' from table_name
    where dataora=@data_max)

    if @val_data_max<>@val_max
    begin
        set @consum=@val_max-@val_data_min+@val_data_max
    end
    else
    begin
        set @consum=@val_data_max-@val_data_min
    end

select @consum'

SELECT (@sql1)
exec(@sql1)