A Sample To Export a Text File with Parameterized Stored Procedure through BCP


  
 
   
  
Use mydb1 --the DB name
go

If object_id('dbo.Classtests','U') is not null
drop table dbo.Classtests;
go--

create table Classtests (ClassID  int,  DepartID int, year_code int, trm_code int)
Insert into Classtests (ClassID,DepartID,year_code ,trm_code )
values(1,1,2015,20),(2,1,2017,20),(3,1,2016,20),(4,1,2016,20),(5,1,2016,20),(6,1,2016,20)
insert into Classtests   
Values (7,1,2017,20),(8,1,2017,20),(9,1,2017,20),(10,1,2017,20)


If object_id('dbo.usp_Classtest','P') is not null
drop procedure dbo.usp_Classtest;
go
create procedure usp_Classtest 
@year_code int
,@trm_code varchar(100)
As
Begin
Select * from Classtests
WHERE year_code=@year_code and trm_code= @trm_code;
End
Go
If object_id('dbo.bcpClassFile','P') is not null
drop procedure dbo.bcpClassFile;
go

Create PROCEDURE bcpClassFile 
 @year_code int
,@trm_code varchar(100)
AS
  BEGIN
	DECLARE  @sql VARCHAR(8000)=''	
	SET @sql = 'bcp "EXEC mydb1..usp_Classtest ''' + CONVERT(VARCHAR(4), @year_code) + ''',''' + @trm_code +  '''" Queryout "c:\temp\test\TheFile'
				 + @trm_code + CONVERT(VARCHAR(4), @year_code)
				 + '.txt " -c -t"\t" -T -S'
				 + @@SERVERNAME
	EXEC master..xp_cmdshell  @sql
  END


  --Test 
  exec bcpClassFile 2016,'20'

  --Check file in  c:\temp\test\TheFile202016.txt

 
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s