We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Conversion failed when converting the nvarchar value

Medium Priority
545 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Perfect...thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.