Autogenerate number using storedprocedure?

[roxcy]

Hi,
I am trying to AutoGenerate a number.Although I could generate the number But I want the output to be string + number format.I have a dropdown which contains name of Cities.So as per my requirement If i select City as Mumbai and submit, output should be Mumbai1.If selected Calcutta it should be Calcutta1 and so on..My Following code Does Gives The required number But How can I generate cityname as well.My table name is CITIES and it consists of id & city column.The values are 1-Mumbai
2-Calcutta
3-Bangalore


DECLARE @NewNo INT

SET NOCOUNT ON

BEGIN TRANSACTION
SELECT @NewNo = LastAutoNo
FROM AUTOREGNO
WHERE TableId = @intTableId

IF @NewNo IS NULL
BEGIN
SELECT @NewNo = 1

INSERT @AUTOREGNO
(
LastAutoNo,
TableID,
)
SELECT @NewNo,
@intTableID
END
ELSE
BEGIN
SELECT @NewNo = @NewNo + 1

UPDATE AUTOREGNO WITH (ROWLOCK)
SET LastAutoNo = @NewNo
WHERE TableId = @intTableId
END

SELECT Mumbai + CONVERT(VARCHAR, @NewNo) AS 'NewNoGenerated'
FROM cities
WHERE cities.ID = @intTableID

COMMIT TRANSACTION



Could someone rectify the error plz...
Thanks..

[russell]

fix the final portion:

SELECT city + CONVERT(VARCHAR(255), @NewNo) AS 'NewNoGenerated'
FROM cities
WHERE cities.ID = @intTableID