If you are using control of flow statements (IF, ELSE, WHILE) and you have more than one command in the block you need to use BEGIN and END.

If I were to do something that way I'd probably do something like this:

-- create table variable for testing

DECLARE @usertest TABLE(userid VARCHAR(50))

DECLARE @id VARCHAR(50)

DECLARE @newid VARCHAR(50)

DECLARE @count INT

-- test data

INSERT INTO @usertest

(

userid

)

SELECT

'user'

UNION ALL

SELECT

'user1'

UNION ALL

SELECT

'user2'

-- return existing test data

SELECT * FROM @usertest

SET @id = 'user'

SET @count = 1

SET @newid = @id

-- get next id

WHILE EXISTS (SELECT 1 FROM @usertest WHERE userid = @newid)

BEGIN

SET @newid = @id + CONVERT(VARCHAR(40), @count)

SET @count = @count + 1

END

-- got newest id insert it

INSERT INTO @usertest

(

userid

)

VALUES

(

@newid

)

SELECT * FROM @usertest