I have a stored procedure that I need to run for multiple locations. I have added a parameter in it for the LocationID, which is a string. I need help setting up a job step which passes it a location ID and outputs it to a path. Below is a screenshot of what I have tried and it did not work.
Declare @cmd varchar(5000),
@LocID = 'NY13'
select @cmd = 'bcp "EXEC ASAP.dbo.usp_Assessments"+ @LocID + queryout "\\appservices.local\LocalationNY13\NY13.csv" -T -c'
exec master..xp_cmdshell @cmd
Look at the screenshot for a picture of the step.
This is the error I received:
Incorrect syntax near '='. [SQLSTATE 42000] (Error 102)
Must declare the scalar variable "@cmd".[SQLSTATE 42000] (Error 137)
Must declare the scalar variable "@cmd".[SQLSTATE 42000] (Error 137). The step failed.
Any help or guidance would be appreciated. Maybe I am not choosing the right option maybe I need to go about this another way?