Additionally you might try something like the following utilizing sqlcmd variables. It allows you to leave the query the same. You just need to add the variables to be used. I think it may allow the query plan to be cached since it actually uses the variables within the sql statement similar to dynamic sql when using sp_executesql.:

DECLARE @curDate datetime

DECLARE @PriorMonthStart datetime

DECLARE @PriorMonthEnd datetime

DECLARE @sqlcmd varchar(1000);

DECLARE @cmd varchar(1000);

SELECT @curDate = GETDate()

--create a variable to use for new line

DECLARE @carReturnLineFeed varchar(10);

SET @carReturnLineFeed = char(13) + char(10); /*carriage return & line feed*/

-- derive last day of the prior month

SET @PriorMonthEnd = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

-- derive first day of prior month

SET @PriorMonthStart = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )-1, 0))

SET NOCOUNT ON;

BEGIN

SET @sqlcmd = 'sqlcmd -Q -v PriorMonthStart=' + CONVERT(VARCHAR(20),@PriorMonthStart,101) + ' PriorMonthEnd=' + CONVERT(VARCHAR(20),@PriorMonthEnd,101)

--** error occurs here

SET @cmd = @carReturnLineFeed

SET @cmd = @cmd + '"DECLARE @PriorMonthStart datetime' + @carReturnLineFeed

SET @cmd = @cmd + 'DECLARE @PriorMonthEnd datetime' + @carReturnLineFeed

SET @cmd = @cmd + 'SET @PriorMonthStart = CAST(''$(PriorMonthStart)'' AS datetime)' + @carReturnLineFeed

SET @cmd = @cmd + 'SET @PriorMonthEnd = CAST(''$(PriorMonthEnd)'' AS datetime)' + @carReturnLineFeed

SET @cmd = @cmd + 'SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate FROM DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed'' AND LoginDate between @PriorMonthStart and @PriorMonthEnd ORDER BY LoginDate" -o C:\AuditReports\FailedLogins.txt'

-- The above Statement gives error - Must declare the scalar variable "@PriorMonthStart"

SET @sqlcmd = @sqlcmd + @cmd;

EXEC xp_cmdshell @sqlcmd,no_output;

--PRINT @sqlcmd

END

EDIT: for the CONVERT of datetime I changed to use 101 - CONVERT(VARCHAR(20),@PriorMonthStart,101) - you may need to choose the specific one that you need.