Error while executing BCP Script - Must declare the scalar variable @v

Question

Description: I have created a stored procedure which will create a pipe separated flat file for the range between from & to id's which are passed as parameters i.e. @from & @to.

The third parameter i.e. @v is a temp variable which will get the incremented value in the loop & this will be used in the where condition of the query.

When I execute the stored procedure in SQL Server, I get an error

Must declare the scalar variable @v

This is my stored procedure:

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

Within your dynamic command there's no @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