You need to pass three parameters, a valid file path,
direction either IN or out, and a valid table name or view name. Using these
parameters, a BCP command is built as a dynamic sql as pass to extended stored
procedure xp_cmdshell.
Listing 6
Create procedure dbo.IMPEXPFROMDB
@filepath varchar(255), --filepath
@direction varchar(5) , --direction(In/Out)
@tablename varchar(255) --Valid tablename/viewname
as
begin
set nocount on
Declare @cmd varchar(1000)
Declare @dbname varchar(1000)
/* Validation for filepath */
If (@filepath is null)
begin
Raiserror('Please enter the FilePath ',16,1)
Return
end
/* Validation for Direction */
If (@direction is null) or (@direction Not In('In','Out'))
begin
Raiserror('Please enter the Direction(In/Out)',16,1)
Return
end
/* Validation for Table name */
If (@tablename is null)
begin
Raiserror('Please enter the Tablename or Viewname',16,1)
Return
end
else if (Object_id(@tablename)is null)
begin
Raiserror('Please enter a Validate Tablename or Viewname',16,1)
Return
end
--Get the database name.
set @dbname = db_name()
--bcp command.
set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -S -U -P'
--To run the bcp using extended stored procedure.
exec master..xp_cmdshell @cmd
set nocount off
end 
