Sql-Server

‘)’ 附近的語法不正確

  • August 27, 2017

我正在嘗試執行以下儲存過程：

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

但是當我執行這段程式碼時：

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

我收到此錯誤消息：

消息 102，級別 15，狀態 1，第 1 行

‘)’ 附近的語法不正確。

這段程式碼工作了一年，現在不行了。我們的版本控制似乎也無濟於事，不幸的是，邏輯對我來說似乎並不簡單。

一種想法是關於 SQL Server 的版本會導致重大更改，但我不相信。

我將如何解決此問題？在涉及動態 sql 時，是否有任何良好的行業實踐來跟踪腳本問題？

我需要驗證破壞程式碼從哪裡開始，不一定是語法錯誤發生在哪裡。

雖然很可能有人更改了您的儲存過程並在此過程中破壞了它，但對這種破壞的最簡單解釋是輸入數據的更改導致它破壞。

在評論中，許多人在各種版本的 SQL Server 中嘗試了您的儲存過程，並且執行成功。這使得問題更有可能是提供給儲存過程的數據發生了變化。

如果您可以想到一個表正在使用您的表之前沒有使用過的數據類型，這可能會導致您的過程開始執行它以前從未需要執行的分支，從而導致錯誤。

您可以使用以下步驟對此進行故障排除：

  1. 您已經有了一個語句，它在執行之前將您執行的動態 SQL 列印到 SSMS 中的“消息”視窗。

仔細查看該視窗中的輸出。是否處理了任何表格，或者您在第一張桌子上失敗了？ 2. 如果您在第一個表上失敗，@script_sql甚至列印一次之前，然後嘗試手動執行儲存過程。使用 SSMS，在對象資源管理器視窗中：

  • 打開與您的伺服器的數據庫引擎連接；
  • 擴展您的伺服器；然後是數據庫；然後是你的數據庫；然後是可程式性；然後是儲存過程。
  • 右鍵點擊您的儲存過程，然後選擇“將儲存過程編寫為”、“創建到”、“新建查詢編輯器視窗”。
  • 在查詢編輯器視窗中，修改創建儲存過程的程式碼，以便您可以直接執行程式碼。刪除或註釋掉該CREATE PROCEDURE行；將參數列表更改為DECLARE語句；並註釋掉或刪除AS.找出你的腳本試圖處理的第一個表名。在您的語句中（或之後的語句中）手動設置@nom_table此表名。現在，從您的儲存過程執行修改後的程式碼。查看它停止的確切位置（如有必要，添加額外的語句以確定它停止的確切位置）。DECLARE``SET``PRINT
  1. 如果您到達@script_sql列印輸出的位置，請嘗試在新的查詢視窗中複製該語句並手動執行*該語句。*如果失敗，請仔細查看 SQL 語句。
  • 獲取 的長度@script_sql，並將其與輸出的內容進行比較；超過一定長度，PRINT將截斷您的值（因此您不會@script_sql在“消息”視窗中獲得全部內容）。您可以通過列印@script_sql
  • 如果您在語句中沒有看到任何明顯的錯誤，請嘗試僅執行部分語句。您可以僅使用第一列創建表嗎？只是前兩個？等等。當您包含其他列時，您最終會發現一個或多個導致問題的列。對於每個測試，從 中的完整程式碼開始@script_sql，以盡量減少通過重複剪切和粘貼引入錯誤的可能性。
  1. 目前的答案之一指出了您的程式碼中的幾個潛在問題；我再指出一個。我不認為這條線：
set  @script_sql= case
                   when left(@script_sql,1)=','
                     then left(@script_sql, LEN(@script_sql) -1)
                   else   @script_sql
                 end  + ') '

正在按您的預期工作。我相信它的目的是在添加右括號之前從 SQL 語句中去除尾隨逗號。但是，它實際上是檢查第一個字元@script_sql是否是逗號，而不是最後一個。試試這個：

set  @script_sql= case
                   when right(@script_sql,1)=','
                     then left(@script_sql, LEN(@script_sql) -1)
                   else   @script_sql
                 end  + ') '
  1. 最後 - 您可以了解是否有人從 SSMS 更改了您的程序。如果您仍然沒有擴展所有內容，請按照上述步驟 2 中的說明將您的伺服器和數據庫擴展至您看到“儲存過程”文件夾的位置。左鍵點擊文件夾，然後轉到“Windows”菜單並選擇“對象資源管理器詳細資訊”。您將看到包含“創建日期”和“上次修改日期”的儲存過程的視圖（如果您沒有看到這些，請右鍵點擊您看到的列的標題，然後選擇這些列以便您可以看到它們。）檢查程序的創建日期和最後修改日期；最近改了嗎？

.sql如果是這樣，那麼希望您在某處的文本或文件中有舊版本的儲存過程；或者，您有數據庫的舊備份（您可以用不同的名稱恢復它，並從備份中編寫儲存過程的腳本。然後您可以刪除舊恢復的數據庫，並嘗試舊版本的儲存過程，以看看它是否有效）。

您還應該使用NVARCHAR所有這些而不是VARCHAR. 並更改@code_colonne VARCHAR(1000)to be的聲明NVARCHAR(MAX)。某些東西可能會被默默地截斷，或者code_colonne是如何建構的，例如空白或靜止NA（而不是 a NULL）Longest導致結果類型為CHAR()or CHAR(NA)。

您還可以執行 Profiler 跟踪，以查看過程執行期間發生的情況。

您的程式碼中有 2 個潛在的“問題”和一個真正的問題。

首先是這樣的：

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

如果表名小於 會發生什麼LEN('39CR_201703')？left您將收到有關傳遞給函式的參數不正確的錯誤。

如果表名等於LEN('39CR_201703')，您將嘗試創建一個名稱以 39 開頭且未引用的表。

所以至少你應該and len(name) > LEN('39CR_201703')在你的游標查詢中添加另一個過濾器：

SELECT name 
FROM sysobjects 
WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR')
and len(name) > LEN('39CR_201703')  
order by name

第二個潛在問題是變數的長度。

即使您聲明了 @script_sqlas varchar(max)，您正在建構的字元串也將被截斷為 8000 個字元，請嘗試使用以下程式碼找出原因：

DECLARE @script_sql varchar(max);
set @script_sql = replicate('a', 10000);
select len(@script_sql);

set @script_sql = replicate(cast('a' as varchar(max)), 10000);
select len(@script_sql);

說這個想像足夠寬的表，具有長欄位名稱，當添加到您的 @script_sql 時超過 8000 個字元。你得到的腳本將被截斷，如果它在指定大小的點被截斷，你會得到這樣的：

create table [AQR_INF_2017T2].[dbo].tblname39CR_201706(col1 int,... colN decimal(10,2)

有未關閉)是因為表定義被截斷了（最後)關閉的十進制類型，不是表定義）

在這種情況下，當您嘗試執行程式碼時，您會收到錯誤

Incorrect syntax near ')'.`

要確定是否是您的情況，只需將您的 proc 添加try..catch到您的 exec 查詢中，但保留PRINT語句。在這種情況下，當 proc 將在 catch 塊中停止完成時，您可以從中列印被截斷的語句

最後一個問題就是不能讓你執行你的動態程式碼，因為你使用了錯誤的語法：

exec @script_sql

不帶括號的 Exec 將嘗試不執行動態程式碼，而是儲存名為'create table...' 的程序因此您的程式碼將停止執行並出現錯誤

Could not find stored procedure 'create table ...

您應該使用以下語法：exec (@script_sql);

引用自：https://dba.stackexchange.com/questions/184077

相關問答

Sql-Server

使用動態 SQL 在不同的數據庫中創建視圖

  • December 2, 2019
檢視問答
Sql-Server

為什麼在儲存過程中的這個查詢上不會發生 SQL 注入？

  • October 16, 2019
檢視問答
Sql-Server

sp_executesql 可以預設配置/使用嗎？

  • June 3, 2019
檢視問答
Sql-Server

在儲存過程中選擇權限？

  • November 27, 2018
檢視問答
Sql-Server

查詢表中的每個數據庫

  • March 22, 2016
檢視問答
Sql-Server

尋找一種更簡潔的方式從多個表中進行選擇

  • November 12, 2015
檢視問答