CODEWITHSUNDEEP
sqlsql-serversql-server-2008
alejandro carnero
alejandro carnero

Reputation: 1784

filename in sql procedure

I have this working in T-SQL

insert into provaxml (arquivo)

sELECT  CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N'C:\2012-09\myfile.xml', SINGLE_BLOB)as arquivo

i m trying to write a procedure :

alter proc adentro (@nome nvarchar(max))as
insert into provaxml  (arquivo)
sELECT  CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N'C:\2012-09\myfile.xml', SINGLE_BLOB)as arquivo

How i must write to change the filename by the variable @nome i have try this way but no works 

 alter proc adentro (@nome nvarchar(max))as
insert into provaxml  (arquivo)
sELECT  CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N @nome, SINGLE_BLOB)as arquivo

Thanks in advance

Alejandro

Upvotes: 0

Views: 695

Answers (2)

alejandro carnero
alejandro carnero

Reputation: 1784

Thanks DeadZone i have write with this code and is working

ALTER proc [dbo].[adenti] (@entra nvarchar(max))as DECLARE @sql varchar(4000); SET @sql = 'INSERT INTO provaxml (arquivo) SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(BULK ''' + @entra + ''', SINGLE_BLOB) as arquivo'; EXEC( @sql );

Upvotes: 0

DeadZone
DeadZone

Reputation: 1690

Try something like this... (I just used this technique for a similar query yesterday)

DECLARE @sql varchar(4000);
SET @sql = 'INSERT INTO provaxml (arquivo) SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(BULK ''' + @nome + ''', SINGLE_BLOB) as arquivo';

EXEC( @sql );

I had this wrapped inside of a cursor (I know... I'm a bad boy for using a cursor. But I think that this was a good use of one) so that I could populate my variable with a different filename each iteration.

Upvotes: 1

Related Questions