Блог

Главная - Вопросы по программированию - Проблема синтаксиса при объявлении оператора select в переменной

Проблема синтаксиса при объявлении оператора select в переменной

#sql #sql-server

#sql #sql-сервер

Вопрос:

Если я запускаю оператор select, он работает нормально, но если я определил переменную, я получаю синтаксическую ошибку.

 Declare @a varchar(2550)

SET @a='    
        SELECT 'ALTER DATABASE '   CAST(DB_NAME() AS VARCHAR(50))   ' MODIFY FILE ( NAME = '  
 QUOTENAME( df.name,'''')   ', NEWNAME = '''   
QUOTENAME( DB_NAME())     
  CASE 
  WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN '.mdf'' )'
  WHEN df.type_desc = 'LOG' THEN '_log.ldf'' )'
  WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN '.ndf'' )'
 END 
FROM sys.database_files df'

 select @a

Ошибка:

Сообщение 102, уровень 15, состояние 1, строка 38 Неправильный синтаксис рядом с ‘ ПРИВЕДЕНИЕ (DB_NAME() КАК VARCHAR(50)) ‘.

Ответ №1:

Это комбинация опечаток и неправильного использования SET and / or SELECT . Если я вас правильно понял, вы можете попробовать использовать следующий оператор:

 DECLARE @a varchar(2550)

SELECT @a = 
   'ALTER DATABASE '  
   CAST(DB_NAME() AS VARCHAR(50))   
   ' MODIFY FILE ( NAME = '  
   QUOTENAME( df.name,'''')   ', NEWNAME = '''   
   QUOTENAME( DB_NAME())   
   CASE 
     WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN '.mdf'' )'
     WHEN df.type_desc = 'LOG' THEN '_log.ldf'' )'
     WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN '.ndf'' )'
  END  
FROM sys.database_files df

SELECT @a

В качестве важного примечания, когда вы используете SELECT @local_variable и оператор SELECT возвращает более одного значения, переменной присваивается последнее возвращаемое значение. Поэтому, если вы хотите сгенерировать сложный оператор для всех файлов базы данных, вам необходимо объединить возвращаемые строки (используя FOR XML PATH or STRING_AGG() ). В этом случае, как прокомментировал @GordonLinoff, вы можете объявить @a переменную как nvarchar(max) :

 DECLARE @a nvarchar(max) = N''

SELECT @a = (
   SELECT 
      N'ALTER DATABASE '  
      CAST(DB_NAME() AS VARCHAR(50))   
      N' MODIFY FILE ( NAME = '  
      QUOTENAME( df.name,'''')   
      N', NEWNAME = '''   
      QUOTENAME( DB_NAME())   
      CASE 
        WHEN df.type_desc = 'ROWS' AND df.file_id = 1 THEN N'.mdf'' )'
        WHEN df.type_desc = 'LOG' THEN N'_log.ldf'' )'
        WHEN df.type_desc = 'ROWS' AND df.file_id != 1 THEN N'.ndf'' )'
      END  
      N';'
   FROM sys.database_files df
   FOR XML PATH('')
) 

SELECT @a

Комментарии:

1. В дополнение к этому прекрасному ответу я также хотел бы предложить альтернативное решение : SET @a = (SELECT ... FROM sys.database_files df) . В некоторых случаях это может быть безопаснее. Или это также может быть объединено с объявлением : DECLARE @a VARCHAR(2550) = (SELECT ... FROM sys.database_files df) .

2. @Zhorov . , , Оператор SQL должен быть объявлен как . NVARCHAR(MAX) Просто нет причин не использовать максимально возможную длину при написании динамического SQL.

3. @GordonLinoff я согласен. Если для объявления нет особой причины Declare @a varchar(2550) и если этот оператор будет выполняться с sp_executesql, помощью nvarchar(max)`, это первый вариант.

Ответ №2:

Я думаю, вы ищете что-то вроде этого

 declare @a nvarchar(max);

with file_cte as (
    select
      N'ALTER DATABASE '   quotename(db_name())   
      N' MODIFY FILE ( NAME = '   quotename( df.name,'''')   
      N', NEWNAME = '''   quotename(db_name())   
        case when df.type_desc = N'ROWS' AND df.file_id = 1 then N'.mdf'' )' 
             when df.type_desc = N'LOG' THEN N'_log.ldf'' )'
             when df.type_desc = N'ROWS' AND df.file_id != 1 then N'.ndf'' )' end string
    from sys.database_files df)
select @a=concat(string_agg(string, ';'), ';') 
from file_cte;

select @a;