# SQL Stored Procedure Bulk Load Syntax Error

I am creating a stored procedure to bulk load a fixed length text file into a SQL table.  I want to pass the name and location of the file to be imported as one parameter and th ename and location of the FORMAT FILE as another parameter.

I created the SP but I'm getting a syntax error on the openrowset statement

Msg 102, Level 15, State 1, Procedure aImportspMuni_DS_BulkInSPORC, Line 27
Incorrect syntax near '@ImportFileNameAndLoc'.

I don't see the error.  Can anyone sport it?

Here's the entire SPROC.
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aImportspMuni_DS_BulkInSPORC]    Script Date: 3/10/2018 11:08:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
Alter PROCEDURE [dbo].[aImportspMuni_DS_BulkInSPORC]
@MuniCode				 Int,
@ImportFileNameAndLoc    nvarchar(250),
@FormatFileNameAndLoc    nvarchar(250)
AS
BEGIN
SET NOCOUNT ON;
--
-- Clear the table to receive the import
--
Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_BulkImport'   -- clear bulk load

--
--
Insert into SQDS_PropertyDesc_BulkImport
select * FROM OPENROWSET( BULK @ImportFileNameAndLoc,FORMATFILE = @FormatFileNameAndLoc) as a

End
--
--  Parse Bulk Loaded Fields Into Import Table
--
Begin

Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_Import'   -- clear Import

INSERT INTO [dbo].SQDS_PropertyDesc_Import (
ControlNumber,
SequenceNumber,
Description
)
SELECT

End

LVL 1
###### Who is Participating?

>> Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_BulkImport'

hope that is just doing a truncate table :)

You will need dynamic SQL for your openrowset. By way of example
declare @source varchar(200) = 'c:\mrwtest\ee\TQD2FILE.txt'
declare @format varchar(200) = 'c:\mrwtest\ee\tqdtfile_format.txt'
declare @sql varchar(max)

set @sql = 'insert yourtable
select * from openrowset( bulk '''+@source+''' ,FORMATFILE = '''+@format+''') as a'

print @sql

exec (@sql)

0

DeveloperCommented:
I haven't used this feature but the documentation (https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql) suggests that filename should be literal. If that is the case you probably will not be able to use expression there.

Just to give you an example, you cannot have

declare @tablename varchar(1000) = 'tblCustomer';
select * from @tablename;

If this is what you want to do, you may have to resort to dynamic SQL instead.

Also failed to understand your begin...end begin...end construct. Confusing.

Also can you just not truncate the table instead of calling a stored procedure to just do that job? Looks like there is a dynamic Sql there.
0

Author Commented:
Yes the called procedure executes a truncate.  Not much need to use that SP in SQL but I use it when I want to clear a SQL table from within my Access program.

Thanks the dynamic SQL you posted works as I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.