[RESOLVED] Problem with SET and SELECT (to assign a local variable)

[dlarkin77]

Hi,

I'm using SQL Server 2005.

I have the following SQL which is supposed to pad an integer value with leading zeros. The problem is that it prints 0 instead of 001

DECLARE @UserID INT
SET @UserID = 1

DECLARE @UtopiaOrderNumber NVARCHAR
SET @UtopiaOrderNumber = (SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3))

PRINT @UtopiaOrderNumber -- prints 0 instead of 001

The following SQL does select the correct value:

DECLARE @UserID INT
SET @UserID = 1

SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3) -- selects 001 as expected

Can anyone let me know what I'm doing wrong?

Thanks,

dlarkin77

[dlarkin77]

My problem was how I declared @UtopiaOrderNumber.

The following works fine:

DECLARE @UserID INT
SET @UserID = 1

DECLARE @UtopiaOrderNumber NVARCHAR(3)
SET @UtopiaOrderNumber = (SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3))

PRINT @UtopiaOrderNumber