Select top 1 from temp table returns only first character-CodePudding

In a T-SQL code block, I am populating a temp table and doing a WHILE loop. In the WHILE loop, when I do a SELECT TOP 1 for a varchar value, it returns only the first character.

Here is the code

BEGIN
    DECLARE @RowCounter int;
    SET @RowCounter = 0;

    DECLARE @TotalRows int;  
    SET @TotalRows = 0;

    DECLARE @tempPcsDataId int; 
    SET @tempPcsDataId = 0;

    -- create a temp table
    -- to stack people: PalletLicensePlate in the Db is varchar(50) so I make tempPalletLicensePlate the same
    DECLARE @DistinctPalletsTempTable TABLE 
            (
                 DeliveryDate datetime, 
                 tempPalletLicensePlate varchar(50), 
                 StoreNumber nvarchar(50), 
                 DerivedWmsCode nvarchar(20), 
                 ShipperClid int, 
                 CartonCount int
            );

    -- populate the temp table
    INSERT @DistinctPalletsTempTable 
        SELECT DISTINCT 
            DeliveryDate, PalletLicensePlate, StoreNumber, 
            DerivedWmsCode, ShipperClid, 0
        FROM
            PcsData 
        WHERE
            InsertGuid = '017DA918-3AF3-4F86-949C-C2611E2BEEE8';

    SET @TotalRows = (SELECT COUNT(*) FROM @DistinctPalletsTempTable);

    WHILE @TotalRows >= @RowCounter
    BEGIN
        DECLARE @CartonCountForThisPallet int; 
        SET @CartonCountForThisPallet = 0;

        DECLARE @ThisLicensePlate varchar;  
        SET @ThisLicensePlate = (SELECT TOP 1 tempPalletLicensePlate 
                                 FROM @DistinctPalletsTempTable);

        SELECT @ThisLicensePlate  -- this always returns B  The first character
        SET @CartonCountForThisPallet = (SELECT COUNT(*) 
                                         FROM PcsData 
                                         WHERE PalletLicensePlate = @ThisLicensePlate);

        UPDATE @DistinctPalletsTempTable 
        SET CartonCount = @CartonCountForThisPallet;

        SELECT @RowCounter;
        SELECT @TotalRows;

        SET @RowCounter = @RowCounter   1;
    END

    SELECT * FROM @DistinctPalletsTempTable;
END

Here is a picture of select * from @DistinctPalletsTempTable; in the code above. You can see that tempPalletLicensePlate is a long string

How can I grab the complete tempPalletLicensePlate from the TOP 1 row?

CodePudding user response：

Well - if you omit any length specification, a SQL variable declare as just varchar just default to 1 character length - this is defined, well documented behavior:

When n isn't specified in a data definition or variable declaration statement, the default length is 1

so no surprise here, really....

And the solution is really simple, too - always explicitly define a length when using varchar as a datatype - for a variable, or a parameter ....

    DECLARE @ThisLicensePlate VARCHAR(50);  -- **DEFINE** then length here!

    SELECT TOP 1 @ThisLicensePlate = tempPalletLicensePlate 
    FROM @DistinctPalletsTempTable;

Now your @ThisLicensePlate will properly show the whole contents of the license plate! The problem really isn't the SELECT TOP 1.... part - it's the declaration of your SQL variable