DECLARE @filename VARCHAR(50)
DECLARE @dash VARCHAR(1)
DECLARE @month VARCHAR(3)
DECLARE @day VARCHAR(2)
DECLARE @year VARCHAR(4)
DECLARE @dlabel VARCHAR(10)
DECLARE @path VARCHAR(50)
DECLARE @fname VARCHAR(50)
DECLARE @Script VARCHAR(2048)

SELECT
    @filename = SUBSTRING(filenames, CHARINDEX('PSM', filenames) ,CHARINDEX('.csv', filenames) - CHARINDEX('PSM', filenames)) , 
    @month = CASE SUBSTRING(filenames, 47, 2)
                 WHEN '01' THEN 'jan' 
                 WHEN '02' THEN 'feb'
                 WHEN '03' THEN 'mar'
                 WHEN '04' THEN 'apr'
                 WHEN '05' THEN 'may'
                 WHEN '06' THEN 'jun'
                 WHEN '07' THEN 'jul'
                 WHEN '08' THEN 'aug'
                 WHEN '09' THEN 'sep'
                 WHEN '10' THEN 'oct'
                 WHEN '11' THEN 'nov'
                 WHEN '12' THEN 'dec'
                 END, 
    @day = SUBSTRING(filenames, 49, 2), 
    @year = SUBSTRING(filenames, 43, 4),
    @dlabel = CONCAT(CASE substring (filenames, 47, 2)
                         WHEN '01' THEN 'jan' 
                         WHEN '02' THEN 'feb'
                         WHEN '03' THEN 'mar'
                         WHEN '04' THEN 'apr'
                         WHEN '05' THEN 'may'
                         WHEN '06' THEN 'jun'
                         WHEN '07' THEN 'jul'
                         WHEN '08' THEN 'aug'
                         WHEN '09' THEN 'sep'
                         WHEN '10' THEN 'oct'
                         WHEN '11' THEN 'nov'
                         WHEN '12' THEN 'dec'
                     END, '_', SUBSTRING(filenames, 49, 2), '_', SUBSTRING(filenames, 43, 4))
FROM  
    dbo.files
WHERE 
    SUBSTRING(filenames, 40, 3) = 'PSM'
    AND filenames LIKE '%.csv%';


SET @fname = 'oars_results_final_' 
SET @filename = CONCAT(@fname, @month, @dash, @day, @dash, @year)
SET @dash = '_'
SET @path = '/Users/Public/'   @filename   '.csv'

SET @script= 'BULK INSERT '    @filename   ' FROM '   @path   
             ' WITH (FIRSTROW = 1, FIELDTERMINATOR = '','', ROWTERMINATOR=''\n'', BATCHSIZE = 250, MAXERRORS = 1)';

EXEC @Script;

Upon execution, I get the following error:

Msg 2812, Level 16, State 62, Line 145
Could not find stored procedure 'BULK INSERT oars_results_final_oct292021 FROM /Users/Public/oars_results_final_oct292021.csv WITH (FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR='\n',BATCHSIZE=250,MAXERRORS=1)'

I am not using the @variable correctly I believe.

CodePudding user response：

To EXECUTE a code in a string, you need to put the string argument in parenthesis.

...
exec(@Script);
...

(Discalimer: I haven't checked if the code in the string is correct or makes any sense.)