执行BCP脚本时出错-必须声明标量变量@v

Question

描述:我已经创建了一个存储过程，它将创建一个管道分隔的平面文件，范围从&对于作为参数传递的id，即@from &@to .

第三个参数@v是一个临时变量，它将在循环中获得增量值&这将用于查询的where条件。

当我在SQL Server中执行存储过程时，我得到一个错误

必须声明标量变量@v

这是我的存储过程:

ALTER PROCEDURE [dbo].[FlatFileCreate]
    @from INT, 
    @to INT,
    @v INT
AS
BEGIN
    WHILE(@from <= @to)
    BEGIN
        SET @v = @from;
        DECLARE @cmd varchar(2000)
        SET @cmd = 'bcp "SELECT * FROM SDB.dbo.Customer WHERE ID=@v  " queryout "c:output_Aug27.txt" -c -T -t "|" -SOLA-DB'
        EXEC master..xp_cmdshell @cmd
        SET @from = @from + 1;  
    END
    RETURN 0
END

Thanks in advance

Answer 1

动态命令中没有@v

ALTER PROCEDURE [dbo].[FlatFileCreate]
    @from INT, 
    @to INT,
    @v INT
AS
BEGIN
    WHILE(@from <= @to)
    BEGIN
        SET @v = @from;
        DECLARE @cmd varchar(2000)
        SET @cmd = 'bcp "SELECT * FROM SDB.dbo.Customer WHERE ID=' + CAST(@v AS VARCHAR(MAX)) + '  " queryout "c:output_Aug' + CAST(@v AS VARCHAR(MAX)) + '.txt" -c -T -t "|" -SOLA-DB'
        EXEC master..xp_cmdshell @cmd
        SET @from = @from + 1;  
    END
    RETURN 0
END