1

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
Improve this question
9
  • 2
    What's the error message? What's the text you get when printing @sql? Commented Aug 12, 2016 at 13:57
  • 2
    what does "doesn't work" means?. Does throw an error?, wrong results?, what? Commented Aug 12, 2016 at 13:57
  • That inner while does not look good... Commented Aug 12, 2016 at 14:20
  • exec sp_executesql expects an nvarchar parameter Commented Aug 13, 2016 at 22:57
  • 1
    @Peter_K - that's because the "everything is fine" SQL that you see is the result of multiple print calls. Each one, by itself generates incomplete SQL, except for the last one. Commented Aug 16, 2016 at 8:08

1 Answer 1

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

When I execute code with your corrections I see only empty Messages section and "Query executed successfuly". Do you have the same result?
I just adjusted the script slightly. "@sqlsub" needs to be "initialized" by "@sqlsub" = '', just the same as "@sql" does. Also "@cnt" should start at 1 since "cnt" never is 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.