How can I execute sql in SQL Server with dynamic query

Question

I have a problem with dynamic SQL. When I use Print (@sql) and manualy copy&paste, it works perfectly, but using exec (@sql) or exec sp_executesql @sql

For this example I used system table sys.types

Do you have any ideas what I'm doing wrong?

CREATE TABLE [dbo].pomocnicza
(okres VARCHAR(5) PRIMARY KEY
, idWiersza INT
, cnt INT
)

INSERT INTO [dbo].pomocnicza(okres, idWiersza, cnt) 
SELECT okres, idWiersza, cnt FROM(SELECT '07_03'okres, 2 idWiersza, 1 cnt
UNION 
SELECT '07_04', 3, 2
UNION
SELECT '07_07', 6, 3
UNION
SELECT '07_10', 9, 4
UNION
SELECT '07_14', 13, 5) t

and dynamic SQL:

 DECLARE @sql VARCHAR(max)
, @sqlSub VARCHAR(max)
, @cnt INT = 0
, @cntSub INT = 2
, @cnt_total INT = 0
, @okres VARCHAR(5)
, @idWiersza INT;

SELECT @cnt_total = COUNT(1) FROM [dbo].pomocnicza

WHILE @cnt <= @cnt_total
BEGIN
   SELECT @okres = okres, @idWiersza = idWiersza FROM [dbo].pomocnicza WHERE cnt = @cnt
   SET @sql = 'select distinct name, schema_id, ''' + @okres
              + ''' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
              '
    WHILE @cntSub <= @idWiersza
    BEGIN
        SET @sqlSub =  @sqlSub + ' or isnull(lead(max_length,' + CAST(@cntSub AS VARCHAR) + ') over (partition by scale,precision order by precision),0)=0'
        SET @cntSub = @cntSub + 1;
    END
       SET @sql = @sql + @sqlSub + ' then 0 else 1 end) all_period_available FROM sys.types'

    if @cnt+1 <= @cnt_total
    begin
        SET @sql = @sql + '
        union all
        ';
    end


   SET @cnt = @cnt + 1;
   SET @sqlSub = ''
   SET @cntSub = 2  

print (@sql)  
exec (@sql)    
END;

Depending what I execute I have different errors

1) only exec (@sql)

Messages:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.

(34 row(s) affected)

Results: 34 rows only from last union

2)

print (@sql)  
exec (@sql)

Messages:

select distinct name, schema_id, '07_03' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_04' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_07' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_10' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'all'.
select distinct name, schema_id, '07_14' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,10) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,11) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,12) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,13) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types

(34 row(s) affected)

Results: 34 rows only from last union

3) only print (@sql) and I have perfectly working SQL:

select distinct name, schema_id, '07_03' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

select distinct name, schema_id, '07_04' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

select distinct name, schema_id, '07_07' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

select distinct name, schema_id, '07_10' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types
        union all

select distinct name, schema_id, '07_14' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
               or isnull(lead(max_length,2) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,3) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,4) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,5) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,6) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,7) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,8) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,9) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,10) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,11) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,12) over (partition by scale,precision order by precision),0)=0 or isnull(lead(max_length,13) over (partition by scale,precision order by precision),0)=0 then 0 else 1 end) all_period_available FROM sys.types

Answer 1

You need to move the EXEC (@sql) outside of the While loop, i.e - your last three rows..

print (@sql)  
exec (@sql)    
END;

..need to change to..

END;
print (@sql)  
exec (@sql)   

Also, your first @sql = statement needs to be @sql = @sql + like you have at the other places. Since you have UNION in your statements you need @sql to be executed only once. In order for this to work you need to set @sql = '' in the beginning of script.

The whole script modified with this will look as such:

DECLARE @sql VARCHAR(max) = ''
, @sqlSub VARCHAR(max) = ''
, @cnt INT = 1
, @cntSub INT = 2
, @cnt_total INT = 0
, @okres VARCHAR(5)
, @idWiersza INT;

SELECT @cnt_total = COUNT(1) FROM [dbo].pomocnicza

WHILE @cnt <= @cnt_total
BEGIN
   SELECT @okres = okres, @idWiersza = idWiersza FROM [dbo].pomocnicza WHERE cnt = @cnt
   SET @sql = @sql + 'select distinct name, schema_id, ''' + @okres
              + ''' as okres, getdate() as czas
                , (case when scale<>0 then 100 else 0 end) scale
                , (case when precision>=4
              '
    WHILE @cntSub <= @idWiersza
    BEGIN
        SET @sqlSub =  @sqlSub + ' or isnull(lead(max_length,' + CAST(@cntSub AS VARCHAR) + ') over (partition by scale,precision order by precision),0)=0'
        SET @cntSub = @cntSub + 1;
    END
       SET @sql = @sql + @sqlSub + ' then 0 else 1 end) all_period_available FROM sys.types'

    if @cnt+1 <= @cnt_total
    begin
        SET @sql = @sql + '
        union all
        ';
    end


   SET @cnt = @cnt + 1;
   SET @sqlSub = ''
   SET @cntSub = 2  

END;
print (@sql)  
exec (@sql)