error while inserting data using dynamic insert datatype varchar cannot convert to int

Question

i have following query,which is used to insert data to table dynamically,i have checked the data type of table and parameter which im passing both are same,but still having following error:

4 1 Msg 245, Level 16, State 1, Line 43 Conversion failed when converting the varchar value 'insert into tblAccessRights (RoleId,CustomerId,IsCustomerExclusive,SubcustomerId,
IsSubCustomerExclusive,ClinicId,CreatedBy,CreatedDate,IsDeleted,IsActive)
values(' to data type int.

this is my query plz check

declare @SubcustomerId varchar(max)    
set @SubcustomerId = '4,5'  

declare @IsSubCustomerExclusive varchar(max)
set @IsSubCustomerExclusive = '1,0'
declare  
@RoleId int ,    
@CustomerId int,    
@IsCustomerExclusive int,    

@ClinicId varchar(max)=null,    
@CreatedBy int,    
@CreatedDate datetime,    
@ModifiedBy int,    
@ModifiedDate datetime,    
@IsDeleted bit,    
@IsActive bit,    
@RoleName varchar(50) 
set  
@RoleId =24     
set @IsCustomerExclusive =0    

set @ClinicId =null  
set @CreatedBy=null  
set @CreatedDate =null   
set @ModifiedDate =null    
set @IsDeleted=0    
set @IsActive =1    
set @RoleName='ts1'   


declare @sql varchar(max)    

while(len(@SubcustomerId) > 0 and LEN(@IsSubCustomerExclusive)>=0)  
begin    
  print left(@SubcustomerId, charindex(',', @SubcustomerId+',')-1)  
  print left(@IsSubCustomerExclusive, charindex(',', @IsSubCustomerExclusive+',')-1) 
 if(LEN(@IsSubCustomerExclusive)=0 )  
 begin  
   set @IsSubCustomerExclusive='0'  
 end  

   set @sql='insert into tblAccessRights        (RoleId,CustomerId,IsCustomerExclusive,SubcustomerId,    
IsSubCustomerExclusive,ClinicId,CreatedBy,CreatedDate,IsDeleted,IsActive)    
values('+@RoleId+','+@CustomerId+','+@IsCustomerExclusive+','+left(@SubcustomerId,   charindex(',', @SubcustomerId+',')-1)+',    
  '+left(@IsSubCustomerExclusive, charindex(',',  @IsSubCustomerExclusive+',')-1)+','+@ClinicId+','+@CreatedBy+','+@CreatedDate+','+@IsDeleted      +','+@IsActive+')'
print @sql    

   --exec(@sql)    
     set @SubcustomerId = stuff(@SubcustomerId, 1, charindex(',', @SubcustomerId+','), '')   
       set @IsSubCustomerExclusive = stuff(@IsSubCustomerExclusive, 1, charindex(',',  @IsSubCustomerExclusive+','), '')     
        end

m i doing wrong in query????,plz help

pid int 
RoleId  int 
CustomerId  int 
IsCustomerExclusive int 
SubcustomerId   varchar(MAX)    
IsSubCustomerExclusive  varchar(MAX)    
ClinicId    varchar(MAX)    
CreatedBy   varchar(50) 
CreatedDate datetime    
ModifiedBy  varchar(50) 
ModifiedDate    datetime    
IsDeleted   bit 
IsActive    bit

this is my table structure

you orobably pass your SQL string as parameter where an int is expected somehwere. — Pleun, Feb 02 '14 at 10:58
Is it mysql or SQL server? You added both tags. SQL server i guess — Pleun, Feb 02 '14 at 10:58

score 0 · Answer 1 · edited May 23 '17 at 12:03

0

for sample:

   SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num
    FROM table
    ORDER BY num;

convert text into number in mysql query

edited May 23 '17 at 12:03

Community

1
1

answered Feb 02 '14 at 11:00

canisLupusLupus · Answer 2 · 2014-02-02T11:36:18.967

0

Your RoleID is an integer, so u try do an implicit cast: 'insert into table ......' as int

you should trying to do somthing like 'foo ' + CONVERT(varchar,@RoleId) + ' ba' than you get: 'foo 24 ba'

http://technet.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx

set @sql='insert into tblAccessRights (RoleId,CustomerId,IsCustomerExclusive,SubcustomerId,
IsSubCustomerExclusive,ClinicId,CreatedBy,CreatedDate,IsDeleted,IsActive)
values(' + CONVERT(varchar, @RoleId) + ',' + CONVERT(varchar, @CustomerId) + ...

edited Feb 02 '14 at 11:36

answered Feb 02 '14 at 11:19

canisLupusLupus

53
4

you declared @RoleID as an int. if u try to concatenate an int with an varchar, the dbms trys to cast the varchar as an int, but you want to cast the other way: int as varchar. – canisLupusLupus Feb 02 '14 at 11:29
look at the refrence: http://technet.microsoft.com/en-us/library/aa276862%28v=sql.80%29.aspx its says: Returns the data type of the argument with the highest precedence. For more information, see Data Type Precedence. – canisLupusLupus Feb 02 '14 at 11:40

error while inserting data using dynamic insert datatype varchar cannot convert to int

2 Answers2