SQL Server中的动态查询执行

I created a stored procedure to take backup of any database in multiple file.

我创建了一个存储过程来备份多个文件中的任何数据库。

This is my stored procedure:

这是我的存储过程：

ALTER PROCEDURE [dbo].[BackupJob]
(
    @FileGroup      NVARCHAR(1000),
    @Path   NVARCHAR(MAX),
    @NoOfFile INT
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQLQuery AS NVarChar(MAX)
    DECLARE @SQLSubQuery AS NVarChar(MAX)
    DECLARE @ParamDefintion AS NVarChar(2000)
    DECLARE @Err Int
    DECLARE @i Int
    DECLARE @Backup_Name NVarChar(max)
    DECLARE @DB_Name NVarChar(max)

    SELECT @DB_Name = DB_NAME()
    SET @i=1;

    -- SELECT statements for procedure here
    SET @SQLQuery = '
    BACKUP DATABASE ['+@DB_Name+'] FILEGROUP = '''+@FileGroup+''' TO  '

    PRINT (@SQLQuery)

    WHILE (@i <= @NoOfFile)
    BEGIN
        SELECT @Backup_Name = @Path + '\'+ DB_NAME()+ '_' + @FileGroup +'_' + CONVERT(VARCHAR(10), GETDATE(), 112)+ '_' + CONVERT(nvarchar,@i)+'.bak'

        SET @SQLSubQuery =ISNULL(@SQLSubQuery,'') + ' DISK = '''+ @Backup_Name + ''''

        IF(@i<@NoOfFile)
        BEGIN
            SET @SQLSubQuery =@SQLSubQuery + ','
        END

        SET @i = @i + 1
    END

    SET @SQLQuery =  @SQLSubQuery + ' WITH CHECKSUM'

    PRINT (@SQLQuery)
    EXECUTE (@SQLQuery)

    SET @Err = @@Error
    RETURN (@Err)
END

If I print the stored procedure and execute it, it works fine, but if I execute the stored procedure like this:

如果我打印存储过程并执行它，它工作正常，但如果我执行这样的存储过程：

exec [BackupJob] 'PRIMARY', 'E:\trybackup',2

I get an error:

我收到一个错误：

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

消息102，级别15，状态1，行1'='附近的语法不正确。

Any suggestion for this ?

有什么建议吗？

Thanks

谢谢

1 个解决方案

#1

Try it again:

再试一次：

ALTER PROCEDURE [dbo].[BackupJob]
(
    -- Add the parameters for the stored procedure here
    @FileGroup      NVARCHAR(1000),
    @Path   NVARCHAR(MAX),
    @NoOfFile INT
)
AS
BEGIN
    DECLARE @SQLQuery AS NVarChar(MAX)
    DECLARE @SQLSubQuery AS NVarChar(MAX)
    DECLARE @ParamDefintion AS NVarChar(2000)
    DECLARE @Err Int
    DECLARE @i Int
    DECLARE @Backup_Name NVarChar(max)
    DECLARE @DB_Name NVarChar(max)


    SELECT @DB_Name = DB_NAME()
    SET @i=1;

    -- SELECT statements for procedure here
    SET @SQLQuery = '
    BACKUP DATABASE ['+@DB_Name+'] FILEGROUP = '''+@FileGroup+''' TO  '

    While( @i<=@NoOfFile)
    BEGIN
        SELECT @Backup_Name = @Path + '\'+ DB_NAME()+ '_' + @FileGroup +'_' + CONVERT(VARCHAR(10), GETDATE(), 112)+ '_' + CONVERT(nvarchar,@i)+'.bak'

        SET @SQLSubQuery =ISNULL(@SQLSubQuery,'') + ' DISK = '''+ @Backup_Name + ''''

        IF(@i<@NoOfFile)
        BEGIN
        SET @SQLSubQuery =@SQLSubQuery + ','
        END
    SET @i = @i + 1
    END

    SET @SQLQuery = @SQLQuery +  @SQLSubQuery   + ' WITH CHECKSUM'
    print(@SQLQuery)
    exec (@SQLQuery)

    SET @Err = @@ERROR
    RETURN (@Err)
    END