CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
user1214695
user1214695

Reputation:

Generate counter with update SQL Server

I have this T-SQL code:

DECLARE @DtsAcumGPSData VARCHAR (50);
SET @DtsAcumGPSData='To'+ @NAutobus + 'DtsAcumGPSData'
--
DECLARE @SQL_DtsAcumGPSData NVARCHAR(MAX);

SET @SQL_DtsAcumGPSData = N'SELECT 
      COUNTER = 0
      ,[iID]
      ,[iVehicleID]
      ,[fTripDistance]
      ,[dtDateTime]
      ,[fLongitude]
      ,[fLatitude]
      ,[bLatLongValid]
      ,[fSpeed]
      ,[bIgnition]
      ,[bStopFlag]
      ,[fTripTime]
      ,[bWorkHours]
      ,[iLogReason]
      ,[bLatLongValidGuess]
      ,[bAux1]
      ,[bAux2]
      ,[bAux3]
      ,[bAux4]
      ,[bAux5]
      ,[bAux6]
      ,[bAux7]
      ,[bAux8]
INTO '+ @DtsAcumGPSData +
N' FROM GPSData 
WHERE iVehicleID ='+ @NAutobus + --245
N'ORDER BY dtDateTime';
EXEC sp_executesql @SQL_DtsAcumGPSData;

-- ##########################################################
-- ACTUALIZANDO CONTADOR
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @SQL_UPDATE_DtsAcumGPSData NVARCHAR (MAX)
SET @SQL_UPDATE_DtsAcumGPSData=
N'UPDATE ' +@DtsAcumGPSData+ N'
SET '+ @COUNTER +N'= COUNTER = ' +@COUNTER+1
EXECUTE sp_executesql @SQL_UPDATE_DtsAcumGPSData

I get this error:

Msg 245, Level 16, State 1, Procedure AutobusGenerico, Line 46
Conversion failed when converting the nvarchar value 'UPDATE To245DtsAcumGPSData SET ' to data type int

Any ideas?

I want to set a counter for column COUNTER

Upvotes: 0

Views: 369

Answers (2)

Taryn
Taryn

Reputation: 247650

When you are using Dynamic SQL, your query is a string so when you concatenate a numeric value to it, you must convert it or you will get the error.

Your @Counter values are int and they need to be converted to be included in your query string:

DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @SQL_UPDATE_DtsAcumGPSData NVARCHAR (MAX)
SET @SQL_UPDATE_DtsAcumGPSData=
N'UPDATE ' +@DtsAcumGPSData+ N'SET '+ cast(@COUNTER as varchar(50)) +N'= COUNTER = ' +cast(@COUNTER+1 as varchar(50))
EXECUTE sp_executesql @SQL_UPDATE_DtsAcumGPSData

Same with this line - it appears that @NAutobus is an int:

WHERE iVehicleID ='+ convert(@NAutobus as varchar(50))

These fields must be converted to a string to be concatenated to your query string.

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

You cannot concatenate number with text unless you convert the number to text

SET @DtsAcumGPSData='To'+ CAST(@NAutobus AS VARCHAR) + 'DtsAcumGPSData'

And

INTO '+ CAST(@DtsAcumGPSData AS VARCHAR) +

And

WHERE iVehicleID ='+ CAST(@NAutobus AS VARCHAR) +

Upvotes: 0

Related Questions