Pass file name as a parameter in procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pass file name as a parameter in procedure

Author

Topic

kevin128
Starting Member

4 Posts

Posted - 2006-03-18 : 08:23:55

Hi Everyone,

I tried to pass file name as a parameter in procedure, but it did work. Here are the codes

create procedure spImport
@filename varchar(100) as
bulk insert mytable
from @filename
end

I received following error message

Msg 102, Level 15, State 1, Procedure spImport, Line 4
Incorrect syntax near '@filename'

Could anybody help me to correct it. Thanks in advance.

Kevin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-18 : 08:32:27

[code]create procedure spImport
@filename varchar(100) as
begin
declare @sql varchar(1000)
select @sql = 'bulk insert mytable from ' + @filename
exec (@sql)
end[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

kevin128
Starting Member

4 Posts

Posted - 2006-03-18 : 13:27:48

Many thanks, Khtan. Your code works perfect.

I have one more question. How can I add a "with option" in the bulk command:

bulk insert ... with(rowterminator='\n')

i.e., how can I put "'" inside ' '? Thanks.

Kevin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 00:24:06

select @sql = 'bulk insert mytable from ' + @filename+' with(rowterminator=''\n'')'

Madhivanan

Failing to plan is Planning to fail

kmsql
Starting Member

4 Posts

Posted - 2007-04-20 : 13:31:45

how do we pass filename as a parameter for a excel file? The procedure uses openrowset function.

SELECT [Co] + [empl id], [DeptID] FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=@FILENAME;IMEX=1', 'select * from [sheet1$]')

yumyum113
Starting Member

31 Posts

Posted - 2007-04-20 : 20:42:03

Something like this?

declare @strSQL as varchar(500)
declare @filename as varchar(255)

set @filename = 'c:\sample.xls'
set @strSQL = 'Insert into ItemsForInboxing'
set @strSQL = @strSQL + ' SELECT workitemid,queue,[user] FROM OpenDataSource(' + quotename('Microsoft.Jet.OLEDB.4.0',char(39)) + ',' + quotename( 'Data Source="' + @filename +'";Extended properties=Excel 5.0',char(39)) + ')...Sheet1$'

exec (@strSQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources