January 5, 2010 at 12:33 pm
Hey guys this is my first post and I'm a junior db developer working on a little project.
I have a stored procedure I'm going to be passing a directory value into
and it looks like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TEST]
@csvVoodoo varchar(255)
AS
drop table #temp
create table #temp(idEmp INT, firstName VARCHAR(50), lastName VARCHAR(50), leader bit, status char(1), fingerPrint VARCHAR(MAX))
BULK INSERT #temp
FROM @csvVoodoo; -- this is the file path
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
the problem occurs on "FROM @csvVoodoo" I get the error
Msg 102, Level 15, State 1, Procedure TEST, Line 7
Incorrect syntax near '@csvVoodoo'.
I've been spinning my wheels on this for awhile so any help is appreciated. Also comments correcting my poor t-sql or best practices is appreciated as well.
January 5, 2010 at 12:40 pm
You'll need to use dynamic SQL to get this to work with a parameterized directory path. Look up EXEC(@sql) in BOL for specifics. Basically, you'll need to build out the BULK INSERT statement into a variable and use EXEC(variable) to execute it. Pretty straight forward.
January 6, 2010 at 6:23 am
This comment really doesn't have anything to do with your question, but does pertain to the code you posted.
You start your stored procedure with Drop table #temp. If the table does not exist, which it shouldn't when the proc is run, you will get an error. Typically you would check for the existence of the table before dropping it:
If Object_Id('tempdb..#temp')
Begin
Drop table #temp
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 6, 2010 at 10:20 am
January 6, 2010 at 11:28 am
Got it working after some research and effort it looks like this.
this is the SP that will be executed by the application
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[updateFingerPrints]
@bulkFile VARCHAR(MAX)
AS
drop table #temp
create table #temp(idEmp INT, firstName VARCHAR(50), lastName VARCHAR(50), leader bit, status char(1), fingerPrint VARCHAR(MAX))
declare @LbulkOut VARCHAR(MAX)
exec spBulkInsertString
@fileName = @bulkFile,
@bulkOut = @LbulkOut OUTPUT
exec(@LbulkOut) -- execute the statement in @LbulkOut
and this is the sp that outputs the bulk insert string
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spBulkInsertString]
-- input @fileName and output @bulkOut
@fileName VARCHAR(255),
@bulkOut VARCHAR(MAX) OUTPUT
AS
-- It seems crazy, but you need all these single quotes to produce the ----statement:
SET @bulkOut = '
BULK INSERT #temp
FROM ' + '''' + @filename + ''''
+ ' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''')'
could you perhaps elaborate why this is necessary?
also
If Object_Id('tempdb..#temp')
Begin
Drop table #temp
End
returns an error for me I have no idea how to deal with
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'Begin'.
January 6, 2010 at 11:48 am
IF Object_Id('tempdb..#temp') IS NOT NULL...
January 6, 2010 at 11:50 am
Oops my bad the code should be:
If Object_Id('tempdb..#temp') Is Not Null
Begin
Drop table #temp
End
It is necessary because the scope of a temp table is the session in which it is called. This means the #temp temp table is dropped when the procedure finishes which means you will get an error when you execute the procedure. The procedure will execute, but your application will return an error. Checking for existence will eliminate that error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 6, 2010 at 12:04 pm
I was asking about why I needed to break the bulk insert as an output string of another SP.
also thanks for the correction in code 🙂
January 6, 2010 at 12:15 pm
mattennevor (1/6/2010)
I was asking about why I needed to break the bulk insert as an output string of another SP.also thanks for the correction in code 🙂
You don't. You can construct the string right in your main SP. It does not have to be a separate SP.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply