Number 0 is not saving to database as a prefix in SQL Server of CHAR data type column

Posted by gmhk on Stack Overflow See other posts from Stack Overflow or by gmhk
Published on 2012-03-26T05:08:38Z Indexed on 2012/03/26 5:29 UTC
Read the original article Hit count: 110

Filed under:

I am trying to insert an value as '019393' into a table with a CHAR(10) column.

It is inserting only '19393' into the database

I am implementing this feature in a stored procedure, doing some manipulation like incrementing that number by 15 and saving it back with '0' as the prefix

I am using SQL Server database

Note: I tried CASTING that value as VARCHAR before saving to the database, but even that did not get the solution

Code

SELECT 
    @fromBSB = fromBSB, @toBSB = toBSB, @type = Type
FROM
    [dbo].[tbl_REF_SpecialBSBRanges]
WHERE
    CAST(@inputFromBSB AS INT) BETWEEN fromBSB AND toBSB

SET @RETURNVALUE =   @fromBSB

IF(@fromBSB = @inputFromBSB)
BEGIN

    PRINT 'Starting Number is Equal';

    DELETE FROM tbl_REF_SpecialBSBRanges 
    WHERE Type = @type AND fromBSB = @fromBSB AND toBSB = @toBSB

    INSERT INTO [tbl_REF_SpecialBSBRanges] ([Type], [fromBSB], [toBSB])
    VALUES(@type, CAST('0' + @fromBSB + 1 AS CHAR), @toBSB)

    INSERT INTO [tbl_REF_SpecialBSBRanges] ([Type], [fromBSB], [toBSB])
    VALUES(@inputBSBName, @inputFromBSB, @inputToBSB)
END

© Stack Overflow or respective owner

Related posts about database