Incorrect syntax near sql ошибка

I’m trying to execute the following stored procedure:

CREATE PROCEDURE  dbo.Compress_taille(@nom_table VARCHAR(64))                                            
AS                       
    PRINT @nom_table                                  
    declare @results table                 
    (                                                        
    TableName varchar(250),                           
    ColumnName varchar(250),                       
    DataType varchar(250),                               
    MaxLength varchar(250),                            
    Longest varchar(250),                   
    SQLText varchar(250),                  
    position float                    
    )                                   
    INSERT INTO @results(TableName,ColumnName,DataType,MaxLength,Longest,SQLText,position)                      
    SELECT                 
        Object_Name(c.object_id) as TableName,        
        c.name as ColumnName,            
        t.Name as DataType,     
        case       
        when t.Name not like  '%char%' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
        end as MaxLength,        
        'NA' as Longest,          
        'SELECT Max(Len([' + c.name + '])) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id) as SQLText,               
        column_id as position   
    FROM                                  
        sys.columns c    
    INNER JOIN                       
        sys.types t ON c.system_type_id = t.system_type_id  
    WHERE                 
        c.object_id = OBJECT_ID(@nom_table)               
        and    t.Name <> 'sysname'   
    order by column_id                       


    DECLARE @position varchar(36)                            
    DECLARE @sql varchar(200)                       
    declare @receiver table(theCount int)                 


    DECLARE cursor_script CURSOR                      
        FOR SELECT position, SQLText FROM @results WHERE MaxLength != 'NA'          
    OPEN cursor_script                        
    FETCH NEXT FROM cursor_script                             
    INTO @position, @sql                  
    WHILE @@FETCH_STATUS = 0                  
    BEGIN                  
        INSERT INTO @receiver (theCount)       
        exec(@sql)        

        UPDATE @results           
        SET Longest = (SELECT theCount FROM @receiver)        
        WHERE position = @position     

        DELETE FROM @receiver             

        FETCH NEXT FROM cursor_script             
        INTO @position, @sql   
    END                       
    CLOSE cursor_script                       
    DEALLOCATE cursor_script                         


    DECLARE @script_sql varchar(max)             


    set @script_sql=' create table [AQR_INF_2017T2].[dbo].'+ left(@nom_table, LEN(@nom_table)-LEN('39CR_201703')) +'39CR_201706('  

    DECLARE                             
            @TableName VARCHAR(80),
            @ColumnName VARCHAR(80),
            @DataType VARCHAR(80),
            @MaxLength VARCHAR(80),
            @Longest VARCHAR(80),
            @code_colonne VARCHAR(1000)


    DECLARE getemp_curs CURSOR                              
    FOR                  
        SELECT TableName, ColumnName, DataType,MaxLength,        
        coalesce(case when Longest='0' then '10' else Longest end ,'1') as Longest,
        position,             
        coalesce(
        case when DataType like '%numer%' then '[' + ColumnName + '] float,'
        when DataType like '%char%' then '[' + ColumnName + '] char(' + coalesce(case when Longest='0' then '10' else Longest end ,'1') + '), ' 
        else '[' +ColumnName + '] ' + DataType+ ',' end,'[' +ColumnName + '] nvarchar(1),')    
        AS code_colonne FROM @results order by position                                           


        OPEN getemp_curs      
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        WHILE @@FETCH_STATUS = 0 BEGIN   

        set  @script_sql=@script_sql + @code_colonne                                               
        FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne          
        END      
    CLOSE getemp_curs                      
    DEALLOCATE getemp_curs                        

    set  @script_sql= case when  left(@script_sql,1)=',' then   left(@script_sql, LEN(@script_sql) -1) else @script_sql end  + ') '                             


    PRINT '@script_sql: ' + @script_sql                                      
    exec @script_sql

GO                         

But I when I execute this code:

DECLARE @table varchar(255)        

DECLARE cursor_test CURSOR                               
    FOR SELECT name FROM sysobjects WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR')  order by name    -- SUPPRIME LES TABLES NON UTILES POUR L'INFOCENTRE AQR  
OPEN cursor_test                       
FETCH NEXT FROM cursor_test                            
INTO @table              
WHILE @@FETCH_STATUS = 0               
BEGIN
    EXEC AQR_INF_2017T2.dbo.Compress_taille @table
    FETCH NEXT FROM cursor_test          
    INTO @table    
END                       
CLOSE cursor_test                   
DEALLOCATE cursor_test

I get this error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

This code was working for one year and now it doesn’t. Our version control does not seem to help either, and, unfortunately, the logic does not seem straightforward to me.

One thought was about the version of SQL Server causing breaking changes, but I am not convinced.

How would I go about troubleshooting this issue? Are there any good industry practices for tracking down script issues when dynamic sql is involved?

I need to verify where the breaking code starts, not necessarily where the syntax error occurs.

Posted by blakhani on August 28, 2014

This entry was posted on August 28, 2014 at 3:30 AM and is filed under SSMS, Troubleshooting.
Tagged: ExecuteNonQuery, incorrect syntax near go, msg 102, SQL, ssms, ssms tips, ssms tips and tricks. You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.