执行带参数SQL Server存储过程时提示无参数错误求助

我为存储过程RestoreBackups声明了参数并传入对应值，但执行时始终报错：

Procedure has no parameters and arguments were supplied.

以下是存储过程代码、执行代码及错误详情：

存储过程代码 #

USE [ns_lots_of_vlfs]
GO

/****** Object:  StoredProcedure [dbo].[RestoreBackups]    Script Date: 16/02/2023 10:49:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Restorebackups] (@DatabaseName     VARCHAR(500),
                                        @DatabaseNameCopy VARCHAR(5000),
                                        @RestoreDate      DATETIME,
                                        @DiffDate         DATETIME = NULL,
                                        @LogDate          DATETIME = NULL)
AS
    DECLARE @FullBackupSQL                 NVARCHAR(max),
            @DiffBackupSQL                 NVARCHAR(max),
            @LogBackupSQL                  NVARCHAR(max),
            @FullBackupDate                DATETIME,
            @FullBackupFile                NVARCHAR(max),
            @DiffBackupFile                NVARCHAR(max),
            @LogbackupFile                 NVARCHAR(max),
            @FullBackupFileCopy            VARCHAR(5000),
            @FullBackupFileCopyldf         VARCHAR(5000),
            @FullBackupFileCopylocationldf VARCHAR(5000)

    SET NOCOUNT ON

    /* Gets the latest full backup to the restore date declared above for the database name declared above.*/
    SELECT TOP 1 @FullBackupFile = bmf.physical_device_name,
                 @FullBackupDate = bs.backup_finish_date
    FROM   msdb.dbo.backupmediafamily bmf
           INNER JOIN msdb.dbo.backupset bs
                   ON bs.media_set_id = bmf.media_set_id
    WHERE  bs.type = 'D'
           AND bs.database_name = @DatabaseName
           AND bs.backup_finish_date <= @RestoreDate
    ORDER  BY backup_finish_date DESC

    IF NOT EXISTS(SELECT 1
                  FROM   files
                  WHERE  physical_device_name = @FullBackupFile)
      INSERT INTO files
                  (family_sequence_number,
                   physical_device_name,
                   mirror,
                   backup_set_id,
                   backup_set_uuid)
      SELECT TOP 1 family_sequence_number,
                   physical_device_name,
                   mirror,
                   backup_set_id,
                   backup_set_uuid
      FROM   msdb.dbo.backupmediafamily bmf
             INNER JOIN msdb.dbo.backupset bs
                     ON bs.media_set_id = bmf.media_set_id
      WHERE  bs.type = 'D'
             AND bs.database_name = @DatabaseName
             AND bs.backup_finish_date <= @RestoreDate
             AND NOT EXISTS (SELECT physical_device_name
                             FROM   files
                             WHERE  @FullBackupFile = physical_device_name)
      ORDER  BY backup_finish_date DESC

    /* Gets me the latest full backup to the database name declared above. for the data file path location */
    SELECT TOP 1 @FullBackupFileCopy =
    Replace(sd.physical_name, @DatabaseName, @DatabaseNameCopy)
    FROM   sys.master_files sd
    WHERE  sd.type_desc = 'ROWS'
           AND sd.physical_name LIKE '%.mdf'
           AND sd.NAME = @DatabaseName

    SELECT TOP 1 @FullBackupFileCopylocationldf =
    Replace(sl.physical_name, @DatabaseName, @DatabaseNameCopy)
    FROM   sys.master_files sl
    WHERE  sl.NAME LIKE '%log'
           AND sl.type_desc = 'LOG'
           AND sl.NAME LIKE '%' + @DatabaseName + '%'

    SELECT TOP 1 @FullBackupFileCopyldf = sl.NAME
    FROM   sys.master_files sl
    WHERE  sl.NAME LIKE '%log'
           AND sl.type_desc = 'LOG'
           AND sl.NAME LIKE '%' + @DatabaseName + '%'

    SELECT TOP 1 @DiffDate = bs.backup_finish_date,
                 @DiffBackupFile = bmf.physical_device_name
    FROM   msdb.dbo.backupmediafamily bmf
           INNER JOIN msdb.dbo.backupset bs
                   ON bs.media_set_id = bmf.media_set_id
    WHERE  bmf.physical_device_name LIKE '%diff%'
           AND bs.backup_finish_date >= @FullBackupDate
           AND bs.database_name = @DatabaseName
           AND bs.backup_finish_date <= @RestoreDate
           AND bs.type = 'I'
    ORDER  BY backup_finish_date DESC

    IF NOT EXISTS(SELECT 1
                  FROM   files
                  WHERE  physical_device_name = @DiffBackupFile)
      INSERT INTO files
                  (family_sequence_number,
                   physical_device_name,
                   mirror,
                   backup_set_id,
                   backup_set_uuid)
      SELECT TOP 1 family_sequence_number,
                   physical_device_name,
                   mirror,
                   backup_set_id,
                   backup_set_uuid
      FROM   msdb.dbo.backupmediafamily bmf
             INNER JOIN msdb.dbo.backupset bs
                     ON bs.media_set_id = bmf.media_set_id
      WHERE  bs.type = 'I'
             AND bs.database_name = @DatabaseName
             AND bs.backup_finish_date <= @RestoreDate
             AND NOT EXISTS (SELECT physical_device_name
                             FROM   files
                             WHERE  @DiffBackupFile = physical_device_name)
      ORDER  BY backup_finish_date DESC

    SET @FullBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy
                         + '  FROM DISK = ''' + @FullBackupFile
                         + ''' WITH  NORECOVERY,  MOVE '''
                         + @DatabaseName + ''' TO '''
                         + @FullBackupFileCopy + ''', MOVE '''
                         + @FullBackupFileCopyldf + '''   TO '''
                         + @FullBackupFileCopylocationldf
                         + ''' , NORECOVERY,NOUNLOAD, STATS = 5'
    SET @DiffBackupSQL = 'RESTORE DATABASE ' + @DatabaseNameCopy
                         + ' FROM  DISK = ''' + @DiffBackupFile
                         + ''' WITH  FILE = 1,  NOUNLOAD,  STATS = 5'

    IF Object_id('tempdb..#TempTable_log') IS NOT NULL
      DROP TABLE #temptable_log

    SELECT bmf.family_sequence_number,
           bmf.media_family_id,
           bmf.media_count,
           bmf.logical_device_name,
           bmf.physical_device_name,
           bmf.device_type,
           bmf.physical_block_size,
           bmf.mirror,
           bs.backup_set_id,
           bs.backup_set_uuid,
           bs.first_family_number,
           bs.first_media_number,
           bs.last_family_number,
           bs.last_media_number,
           bs.catalog_family_number,
           bs.catalog_media_number,
           bs.position,
           bs.expiration_date,
           bs.software_vendor_id,
           bs.NAME,
           bs.description,
           bs.user_name,
           bs.software_major_version,
           bs.software_minor_version,
           bs.software_build_version,
           bs.time_zone,
           bs.mtf_minor_version,
           bs.first_lsn,
           bs.last_lsn,
           bs.checkpoint_lsn,
           bs.database_backup_lsn,
           bs.database_creation_date,
           bs.backup_start_date,
           bs.backup_finish_date,
           bs.type,
           bs.sort_order,
           bs.code_page,
           bs.compatibility_level,
           bs.database_version,
           bs.backup_size,
           bs.database_name,
           bs.server_name,
           bs.machine_name,
           bs.flags,
           bs.unicode_locale,
           bs.unicode_compare_style,
           bs.collation_name,
           bs.is_password_protected,
           bs.recovery_model,
           bs.has_bulk_logged_data,
           bs.is_snapshot,
           bs.is_readonly,
           bs.is_single_user,
           bs.has_backup_checksums,
           bs.is_damaged,
           bs.begins_log_chain,
           bs.has_incomplete_metadata,
           bs.is_force_offline,
           bs.is_copy_only,
           bs.first_recovery_fork_guid,
           bs.last_recovery_fork_guid,
           bs.fork_point_lsn,
           bs.database_guid,
           bs.family_guid,
           bs.differential_base_lsn,
           bs.differential_base_guid,
           bs.compressed_backup_size,
           bs.key_algorithm,
           bs.encryptor_thumbprint,
           bs.encryptor_type
    INTO   #temptable_log
    FROM   msdb.dbo.backupmediafamily bmf
           INNER JOIN msdb.dbo.backupset bs
                   ON bs.media_set_id = bmf.media_set_id
    WHERE  bs.database_name = @DatabaseName
           AND bs.backup_finish_date <= @RestoreDate
           AND bs.backup_finish_date > @FullBackupDate
           AND bs.backup_finish_date > Isnull(@DiffDate, @FullBackupDate)
           AND bs.type = 'L'

    IF NOT EXISTS(SELECT 1
                  FROM   files
                  WHERE  physical_device_name = @LogbackupFile)
      INSERT INTO files
      SELECT *
      FROM   #temptable_log
      WHERE  physical_device_name NOT IN (SELECT physical_device_name
                                          FROM   files)

    DECLARE @Counter INT

    SET @Counter = 1

    WHILE ( @Counter <= (SELECT Count(*)
                         FROM   #temptable_log) )
      BEGIN
          SELECT TOP 1 @LogbackupFile = physical_device_name,
                       @LogBackupSQL = @LogbackupFile
          FROM   #temptable_log
          WHERE  backup_finish_date > Isnull(@DiffDate, @FullBackupDate)
                 AND backup_finish_date > @FullBackupDate
                 AND backup_finish_date <= @RestoreDate
          ORDER  BY backup_finish_date;

          SET @Counter = 1 -- This should start at one -- 
          /*every time you enter the while loop @counter is a new value so 1 then 2 then 3 and so on which retrieves this from the 
            temp table log.
          */
          SET @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy
                              + ' FROM  DISK = ''' + @LogbackupFile
                              + ''' WITH  FILE = 1,  NOUNLOAD,  STATS = 5,'

          DELETE FROM #temptable_log
          WHERE  physical_device_name = @LogbackupFile;
      END

    EXEC master..Sp_executesql
      @FullBackupSQL,
      @DiffBackupSQL,
      @LogBackupSQL 

执行代码 #

USE [ns_lots_of_vlfs]
GO

DECLARE 
     @DatabaseName varchar(500) = 'ns_lots_of_vlfs',
     @DatabaseNameCopy varchar(5000) = 'ns_lots_of_vlfs_COPY',
     @RestoreDate datetime = '2023-02-13 15:45:00.000',
     @DiffDate datetime = null,
     @LogDate datetime = null

EXECUTE [dbo].[RestoreBackups] @DatabaseName, @DatabaseNameCopy, @RestoreDate
GO

错误提示 #

Procedure has no parameters and arguments were supplied.

问题排查与修复 #

1. 核心原因：存储过程名称大小写不匹配 #

你的存储过程定义的名称是[dbo].[Restorebackups]（字母b为小写），但执行时调用的是[dbo].[RestoreBackups]（字母B为大写）。在区分大小写的数据库排序规则下，SQL Server会将这两个视为完全不同的对象，导致你实际调用的是一个不存在的、无参数的存储过程（或旧版本无参数的同名存储过程）。

2. 修复方案 #

• 方案一：统一调用名称
  执行时使用和定义完全一致的名称：

  EXECUTE [dbo].[Restorebackups] @DatabaseName, @DatabaseNameCopy, @RestoreDate
  

• 方案二：修改存储过程名称
  重新修改存储过程的名称为RestoreBackups（保持大小写一致）：

  ALTER PROCEDURE [dbo].[RestoreBackups] (@DatabaseName     VARCHAR(500),
                                          @DatabaseNameCopy VARCHAR(5000),
                                          @RestoreDate      DATETIME,
                                          @DiffDate         DATETIME = NULL,
                                          @LogDate          DATETIME = NULL)
  -- 后续代码保持不变
  

3. 额外检查：确认存储过程存在性 #

执行以下语句，确认当前数据库中存储过程的正确名称：

SELECT name FROM sys.procedures WHERE name LIKE 'Restore%'

确保调用的名称与查询结果完全一致。

4. 附带问题：动态SQL执行逻辑错误 #

存储过程最后执行动态SQL的方式有误，sp_executesql只能执行单条语句，不能同时传入多个SQL变量。同时循环逻辑存在死循环问题（@Counter始终被设为1），修正后的执行逻辑如下：

-- 执行全量备份恢复
EXEC sp_executesql @FullBackupSQL;
-- 若差异备份存在则执行
IF @DiffBackupSQL IS NOT NULL
    EXEC sp_executesql @DiffBackupSQL;
-- 执行日志备份恢复（修正循环逻辑）
DECLARE @Counter INT = 1;
DECLARE @TotalLogs INT = (SELECT COUNT(*) FROM #temptable_log);
WHILE @Counter <= @TotalLogs
BEGIN
    SELECT TOP 1 @LogbackupFile = physical_device_name FROM #temptable_log ORDER BY backup_finish_date;
    SET @LogBackupSQL = 'RESTORE LOG ' + @DatabaseNameCopy + ' FROM DISK = ''' + @LogbackupFile + ''' WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY;';
    EXEC sp_executesql @LogBackupSQL;
    DELETE FROM #temptable_log WHERE physical_device_name = @LogbackupFile;
    SET @Counter = @Counter + 1;
END
-- 最后恢复数据库可访问状态
EXEC sp_executesql 'RESTORE DATABASE ' + @DatabaseNameCopy + ' WITH RECOVERY;';

内容的提问来源于stack exchange，提问作者Kushal