Click here to Skip to main content
15,742,120 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am writing PL/SQL code for update specific table based on condition.
I want to replace
"NA" +@cnt
value to NA1, NA2, NA3 and so on.
so I written following code. in SQL Server 2012.


DECLARE @cnt INT = 1;
DECLARE @Val varchar(255);

set @S#No=(select max(S#No) from [dbo].[AD_Data_Ruf])
print @S#No
WHILE @cnt < @S#No
DECLARE @EmailAddress nvarchar(250)

set @EmailAddress=(select [Email Address] from [dbo].[AD_Data_Ruf] where S#No = @cnt)
   --PRINT 'Inside simulated FOR LOOP on';
   if(@EmailAddress='"NA" +@cnt')
   set @Val = 'NA' +@cnt;
   update [dbo].[AD_Data_Ruf] set  [Email Address] =@Val 

    PRINT @EmailAddress
   SET @cnt = @cnt + 1;

--PRINT 'Done simulated FOR LOOP on';

but It showing error at
set @Val = 'NA' +@cnt;

What I have tried:

SET Values to variable to static string + variable value.
as follows.

set @Val = 'NA' +@cnt;

where NA is static Value where as
is variable.

but it showing error as
Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the varchar value 'NA' to data type int.

data type of
variable is varchar(250).

kindly suggest where I should make modification in code
Updated 5-Feb-20 2:17am
Richard MacCutchan 5-Feb-20 8:17am    
You need to convert the integer value to a string to concatenate the two values.

1 solution

You cannot just add a string and a number in SQL. You have to cast the number to a string.
set @Val = 'NA' + CAST(@cnt AS varchar(12));
Share this answer
MadMyche 5-Feb-20 9:52am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900