Can I assign value to variable or parameter after the execution code is defined?

Question

I have a quite large script which is shrunk and simplified in this question.The overall principal is that I have some code that need to be run several times with only small adjustments for every iteration. The script is built with a major loop that has several subloops in it. Today the whole select-statement is hard coded in the loops. My thought was that I could write the select-statement once and only let the parts that needs to be changed for every loop be the only thing that changes in the loop. The purpose is easier maintaining.

Example of the script:

declare
    @i1 int,
    @i2 int,
    @t nvarchar(50),
    @v nvarchar(50),
    @s nvarchar(max)

set @i1 = 1

while @i1 < 3
begin

    if @i1 = 1
    begin

        set @i2 = 1
        set @t = 'Ansokningsomgang'

        set @s = '
            select ' + @v + '_Ar,count(*) as N
            from (
                select left(' + @v + ',4) as ' + @v + '_Ar
                from Vinnova_' + @t + '
                ) a
            group by ' + @v + '_Ar
            order by ' + @v + '_Ar
            '
        
        while @i2 < 4
        begin

            if @i2 = 1
            begin

                set @v = 'diarienummer'

                exec sp_executesql
                    @stmt = @s,
                    @params = N'@tab as nvarchar(50), @var as nvarchar(50)',
                    @tab = @t, @var = @v

            end
            else if @i2 = 2
            begin

                set @v = 'utlysning_diarienummer'

                exec sp_executesql
                    @stmt = @s,
                    @params = N'@tab as nvarchar(50), @var as nvarchar(50)',
                    @tab = @t, @var = @v

            end
            else if @i2 = 3
            begin

                set @v = 'utlysning_program_diarienummer'

                exec sp_executesql
                    @stmt = @s,
                    @params = N'@tab as nvarchar(50), @var as nvarchar(50)',
                    @tab = @t, @var = @v

            end

            set @i2 = @i2 + 1

        end

    end
    else
        
        print('Nr: ' + cast(@i1 as char))

    set @i1 = @i1 + 1

end

This script doesn't work. It runs through but have no outputs. If I declare @v above the declaration of @s it works, but then I need to declare @s for every time I need to change the value for @v. Then there is no point in doing this.

@i1 iterates far more times than what is shown here.

The else statement to "if @i1" doesn't exist in the real script. It replaces a bunch of subloops that run for every value that is aloud for @i1 in this example.

I also tried to just execute @s like:

exec(@s)

in every loop. Same result.

So what am I missing?

Database engine is MS SQL Server.

Answer 1

Your parallel-structured tables are not 'normalized' to any degree, and you are now suffering the consequence. Typically, the best approach is to go ahead and make the data more normalized before you take any other action.

Dynamic sql could work for making this task easier, and it is okay as long as it's an ad-hoc task that hopefully you use to begin building permanent tables in the name of making your various parallel tables obsolete. It is not okay if it is part of a regular process because someone could enter in some malicious code into one of your table values and do some damage. This is particularly true in your case because your use of left functions imply that you're columns are character based.

Here's some code to put your data in more normal form. It can be made more normal after this, so it would only be the first step. But it gets you to the point where using it for your purpose is far easier, and so hopefully will motivate you to redesign.

-- plug in the parallel tables you want to normalize
declare @tablesToNormalize table (id int identity(1,1), tbl sysname);
insert @tablesToNormalize values ('Ansokningsomgang', 'Ansokningsomgang2');

-- create a table that will hold the restructured data
create table ##normalized (
    tbl sysname,
    rowKey int, -- optional, but needed if restructure is permanent
    col sysname,
    category varchar(50),
    value varchar(50)
);

-- create template code to restructure and insert a table's data 
-- into the normalized table (notice the use of @tbl as a string, 
-- not as a variable)
declare @templateSql nvarchar(max) = '
    insert      ##normalized
    select      tbl = ''Vinnova_@tbl'',
                rowKey = t.somePrimaryKey, -- optional, but needed if restructure is permanent
                ap.col,
                category = left(ap.value, 4),
                ap.value
    from        Vinnova_@tbl t
    cross apply (values
                    (''diarienummer'', diarienummer),
                    (''utlysning_diarienummer'', utlysning_diarienummer),
                    (''utlysning_program_diarienummer'', utlysning_program_diarienummer)
                    // ... and so on (much better than writing a nested loop for ever row)
                ) ap (col, value)
';

-- loop the table names and run the template (notice the 'replace' function)
declare @id int = 1;
while @id <= (select max(id) from @tablesToNormalize) 
begin
    declare @tbl sysname = (select tbl from @tablesToNormalize where id = @id);
    declare @sql nvarchar(max) = replace(@templateSql, '@t', @tbl); 
    exec (@tbl);
end

Now that your data is in a more normal form, code for your purpose is much simpler, and the output far cleaner.

select      tbl, col, category, n = count(value)
from        ##normalized
group by    tbl, col, category
order by    tbl, col, category;