CODEWITHSUNDEEP
sqlt-sql
Bulvak
Bulvak

Reputation: 1804

Transact SQL, how can I take varchar type, convert to int, add one, convert back to string, then store?

ALTER PROCEDURE [dbo].[AmendInsertDuplicateFields] (@ReportID varchar(50))
AS
BEGIN
    DECLARE @NewReportID VARCHAR(50)
    SET @NewReportID = NEWID()

    INSERT INTO [MVCOmar].[dbo].[PrideMVCCollisionBegin]
           ([ReportID], [LocalIncidentNum], [version], [MTOReferenceNo], [Submitted]) 
       SELECT 
          @NewReportID, [LocalIncidentNum], [version], [MTOReferenceNo], [Submitted] 
       FROM 
          [MVCOmar].[dbo].[PrideMVCCollisionBegin] 
       WHERE 
          [ReportID] = @ReportID;

I would like to take the result that I get for version, convert it from string to int type, add one, convert back to string, and store it.

I acknowledge that version should be int and not string type. I also acknowledge that an even better method of accomplishing this would be to set properties to increment by one.

I can't do either of those option for the time being because my priorities are different right now, I am time limited, the code is very old, and written by numerous people which carried poor coding habits.

Upvotes: 0

Views: 1467

Answers (3)

Dan J
Dan J

Reputation: 16718

You can do this inline in your SELECT statement, using the CONVERT function:

INSERT INTO [MVCOmar].[dbo].[PrideMVCCollisionBegin] ([ReportID], [LocalIncidentNum], [version], [MTOReferenceNo], [Submitted]) 
SELECT @NewReportID, [LocalIncidentNum], 
       CONVERT(VARCHAR, (CONVERT(INT, [version]) + 1)), 
       [MTOReferenceNo], [Submitted] 
FROM [MVCOmar].[dbo].[PrideMVCCollisionBegin] 
WHERE [ReportID]=@ReportID;

Upvotes: 2

Ramesh Manni
Ramesh Manni

Reputation: 106

SQL Server supports CAST or CONVERT

CAST(col1 as int)

CONVERT(int,col1)

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135161

you don't need to convert it, run this, there will be an implicit conversion

SELECT '1' + 1

That returns 2

In your case you can just do [version] + 1

Upvotes: 4

Related Questions