Click here to Skip to main content
15,897,891 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.

[Integration]

DECLARE @cnt INT = 1;
DECLARE @S#No INT = 0;
DECLARE @Val varchar(255);

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

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

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

--PRINT 'Done simulated FOR LOOP on TechOnTheNet.com';
GO



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
@cnt
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
@Val
variable is varchar(250).

kindly suggest where I should make modification in code
Posted
Updated 5-Feb-20 2:17am
Comments
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.
SQL
set @Val = 'NA' + CAST(@cnt AS varchar(12));
 
Share this answer
 
v2
Comments
MadMyche 5-Feb-20 9:52am    
+5

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