Dynamic varchar variable

It seems to me that you are just wanting to return an integer with leading zeroes to form a 6 character string.

Something like this would achieve what you want:

DECLARE @i int
SET @i = 10

SELECT  'B' + RIGHT('0000' + CAST(@i AS varchar(50)), 4)

You could wrap this into a function to make the code re-usable and also allow for different lengths etc. This should put you on the right track though.

You need to let us know how the implementation would be? If you would pass an integer then it could be done as

DECLARE @Dynamic CHAR(5), @Int SMALLINT

SET @Int = 1

SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)

SELECT @Dynamic

SET @Int = 101

SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)

SELECT @Dynamic

SET @Int = 1010

SELECT @Dynamic = 'B' + RIGHT('0000'+ CAST(@Int AS VARCHAR(50)),4)

SELECT @Dynamic

You can use stuff to solve the problem, but you should reconsider if you really should do it. A varchar(5) takes 7 bytes and an integer only takes 4 bytes and that may affect the performance.

SELECT STUFF('B0000', 6-LEN(b),LEN(b),b) b FROM (VALUES('1'),('2'),('1003'),('12')) AS a(b)
ORDER BY B