CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
sai bharath
sai bharath

Reputation: 844

Finding MAX value of data type Varchar

Firstly I have a table tblSample with ID as column of datatype INT. To auto generate ID for every transaction, I created a stored procedure:

DECLARE @Id INT

SELECT @Id = MAX(Id) 
FROM tblSample 

IF @Id IS NULL
BEGIN
    SELECT 0 as Id
END
ELSE 
    SELECT MAX(Id) as ID FROM tblSample

Here as you observe if ID has no rows MAX(Id)=0 then return 0 or else return MAX(ID) value so that next insertion will be greater than max(ID).

That's fine but now I had column ID with datatype VARCHAR I have to do similar operation how can I that?

Upvotes: 0

Views: 276

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

The code looks fine so it should work with VARCHAR also but my suggestion is to use storage variable also with same datatype so it won't get conflicted anywhere in the operation:

DECLARE @Id VARCHAR(10)

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

DECLARE @Id INT
SELECT @Id=MAX(Id) FROM tblSample 
IF @Id IS NULL
BEGIN
 SELECT 'Your_VarCharValue' + CAST(0 AS VARCHAR) as Id
END
ELSE 
 SELECT 'Your_VarCharValue' + CAST(MAX(Id) AS VARCHAR) as ID FROM tblSample

Upvotes: 0

Raul Cuth
Raul Cuth

Reputation: 2469

I think you could use MAX(CAST(varcharcolumn AS Int))

Upvotes: 1

Related Questions