• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

Conversion failed when converting the nvarchar value

Hello Experts,

I am trying to use a dynamic sql statement for an insert.  On 3 of the variables, I am receiving the following:

Conversion failed when converting the nvarchar value

The lines that are commented out are the ones giving me the trouble.  I did try doing a convert similar to the other ones, but it is not working.

thanks,




declare @max_batch_id	varchar(9)
 
declare   @get_max_id		nvarchar(2000)
		, @insert_batch_id	nvarchar(4000)
 
 
declare	  @b_tax_year			varchar(4)
		, @b_batch_type			varchar(4)
		, @b_batch_id			int
		, @b_job_name			varchar(60)
		, @b_pave_needed		char(1)
		, @b_pave_completed		char(1)
		, @b_extract_date		datetime
		, @b_mail_date			datetime
		, @b_affidavit			int
		, @b_renotice_flg		varchar(1)
		, @b_pdf_ready			varchar(1)
		, @b_pdf_attached		varchar(1)
		, @b_pdf_attached_dt	datetime
		, @b_mkdir_flg			char(1)
 
 
set @b_tax_year			= @p_tax_year			
set @b_batch_type		= 'XXX'				
set @b_batch_id			= @max_batch_id + 1 --increase the batch id by 1
set @b_job_name			= 'Mail Print ' + cast(@p_roll_id as varchar(8))
set @b_pave_needed		= 'N'
set @b_pave_completed	= 'Y'
set @b_extract_date		= @p_arb_rls_dt	
set @b_mail_date		= @p_arb_ord_mail_dt
set @b_affidavit		= NULL
set @b_renotice_flg		= 'N'	
set @b_pdf_ready		= 'N'
set @b_pdf_attached		= 'N'
set @b_pdf_attached_dt	= NULL
set @b_mkdir_flg		= NULL
 
 
--insert the batch id record into the table
select @insert_batch_id = N'insert into ' + @database + N'..hc_batches ' --(tax_year,batch_type,batch_id,job_name,pave_needed,pave_complete,extract_date,mail_date,affidavit,renotice_flg,pdf_ready,pdf_attached,pdf_attached_dt ,mkdir_flg) '
select @insert_batch_id = @insert_batch_id + N' values(' + @b_tax_year  
select @insert_batch_id = @insert_batch_id + N', ''' + @b_batch_type + N''' '
select @insert_batch_id = @insert_batch_id + N', ' + convert(varchar(9), @b_batch_id, 101)  
select @insert_batch_id = @insert_batch_id + N', ''' + @b_job_name + N''' '   
select @insert_batch_id = @insert_batch_id + N', ''' + @b_pave_needed + N''' '  
select @insert_batch_id = @insert_batch_id + N', ''' + @b_pave_completed  + N''' '
select @insert_batch_id = @insert_batch_id + N', ' + convert(varchar(10), @b_extract_date, 101)   
select @insert_batch_id = @insert_batch_id + N', ' + convert(varchar(10), @b_mail_date, 101)   
--select @insert_batch_id = @insert_batch_id + N', ' + @b_affidavit    
select @insert_batch_id = @insert_batch_id + N', ''' + @b_renotice_flg + N''' '  
select @insert_batch_id = @insert_batch_id + N', ''' + @b_pdf_ready + N''' ' 
select @insert_batch_id = @insert_batch_id + N', ''' + @b_pdf_attached + N''' '  
--select @insert_batch_id = @insert_batch_id + N', ' + convert(varchar(10), @b_pdf_attached_dt, 101)   
--select @insert_batch_id = @insert_batch_id + N', ' + @b_mkdir_flg + N') '
 
print '@insert_batch_id = '  + @insert_batch_id
 
exec sp_executesql @insert_batch_id

Open in new window

0
thewayne73
Asked:
thewayne73
1 Solution
 
BrandonGalderisiCommented:
Since the only thing that can't be done without dynamic SQL is the @database, let's keep that dynamic and use the power of sp_executeSQL to handle your dynamic SQL.
declare @max_batch_id   varchar(9)
 
declare   @get_max_id           nvarchar(2000)
                , @insert_batch_id      nvarchar(4000)
 
 
declare   @b_tax_year                   varchar(4)
                , @b_batch_type                 varchar(4)
                , @b_batch_id                   int
                , @b_job_name                   varchar(60)
                , @b_pave_needed                char(1)
                , @b_pave_completed             char(1)
                , @b_extract_date               datetime
                , @b_mail_date                  datetime
                , @b_affidavit                  int
                , @b_renotice_flg               varchar(1)
                , @b_pdf_ready                  varchar(1)
                , @b_pdf_attached               varchar(1)
                , @b_pdf_attached_dt    datetime
                , @b_mkdir_flg                  char(1)
               ,@params nvarchar(max)
 
set @b_tax_year                 = @p_tax_year                   
set @b_batch_type               = 'ARB'                         
set @b_batch_id                 = @max_batch_id + 1 --increase the batch id by 1
set @b_job_name                 = 'ARB HEARING RELEASE ' + cast(@p_roll_id as varchar(8))
set @b_pave_needed              = 'N'
set @b_pave_completed   = 'Y'
set @b_extract_date             = @p_arb_rls_dt 
set @b_mail_date                = @p_arb_ord_mail_dt
set @b_affidavit                = NULL
set @b_renotice_flg             = 'N'   
set @b_pdf_ready                = 'N'
set @b_pdf_attached             = 'N'
set @b_pdf_attached_dt  = NULL
set @b_mkdir_flg                = NULL
 
 
--insert the batch id record into the table
select @insert_batch_id = N'insert into ' + @database + N'..hc_batches (tax_year,batch_type,batch_id,job_name,pave_needed,pave_complete,extract_date,mail_date,affidavit,renotice_flg,pdf_ready,pdf_attached,pdf_attached_dt ,mkdir_flg) 
values(@b_tax_year,@b_batch_type,@b_batch_id,@b_job_name,@b_pave_needed,@b_pave_completed,@b_extract_date,@b_mail_date,@b_renotice_flg ,@b_pdf_ready ,@b_pdf_attached)'
 
 set @params = '@b_tax_year                   varchar(4)
                , @b_batch_type                 varchar(4)
                , @b_batch_id                   int
                , @b_job_name                   varchar(60)
                , @b_pave_needed                char(1)
                , @b_pave_completed             char(1)
                , @b_extract_date               datetime
                , @b_mail_date                  datetime
                , @b_affidavit                  int
                , @b_renotice_flg               varchar(1)
                , @b_pdf_ready                  varchar(1)
                , @b_pdf_attached               varchar(1)
                , @b_pdf_attached_dt    datetime
                , @b_mkdir_flg                  char(1)'
 
 
 
exec sp_executesql @insert_batch_id, @params, 
@b_tax_year          
, @b_batch_type      
, @b_batch_id        
, @b_job_name        
, @b_pave_needed     
, @b_pave_completed  
, @b_extract_date    
, @b_mail_date       
, @b_affidavit       
, @b_renotice_flg    
, @b_pdf_ready       
, @b_pdf_attached    
, @b_pdf_attached_dt 
, @b_mkdir_flg       

Open in new window

0
 
thewayne73Author Commented:
Perfect...thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now