Default Value of Decimal RRS feed

  • Question

  • User-797751191 posted


      I have below table and decimal fields null value . I want it should display as Blank . 

    CREATE TABLE #tbl1(BpName nvarchar(100),
    "1" Decimal(10,2),"2" Decimal(10,2),"3" Decimal(10,2),"4" Decimal(10,2),"5" Decimal(10,2),"6" Decimal(10,2),
    "7" Decimal(10,2),"8" Decimal(10,2),"9" Decimal(10,2),"10" Decimal(10,2),"11" Decimal(10,2),"12" Decimal(10,2),
    "13" Decimal(10,2),"14" Decimal(10,2),"15" Decimal(10,2),"16" Decimal(10,2),"17" Decimal(10,2),"18" Decimal(10,2),
    "19" Decimal(10,2),"20" Decimal(10,2),"21" Decimal(10,2),"22" Decimal(10,2),"23" Decimal(10,2),"24" Decimal(10,2),
    "25" Decimal(10,2),"26" Decimal(10,2),"27" Decimal(10,2),"28" Decimal(10,2),"29" Decimal(10,2),"30" Decimal(10,2),
    "31" Decimal(10,2))


    Monday, July 20, 2020 10:00 AM

All replies

  • User475983607 posted

    I have below table and decimal fields null value . I want it should display as Blank . 

    A decimal datatype cannot be an empty string.  You'll need to create a query that convert the column to a string and uses ISNULL() or COALESCE()  to return an empty string when the field has a NULL value.

    IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL
    	DROP TABLE #tbl1
    CREATE TABLE #tbl1(
    	BpName nvarchar(100),
    	"1" Decimal(10,2),
    	"2" Decimal(10,2)
    INSERT INTO #tbl1 (BpName, [1], [2])
    VALUES ('test1', NULL, 3.25), ('test2', 1.23, NULL)
    SELECT BpName,
    	ISNULL(CAST([1] AS VARCHAR(10)), ''),
    	COALESCE(CAST([2] AS VARCHAR(10)), '')
    FROM #tbl1

    Monday, July 20, 2020 11:01 AM
  • User-797751191 posted


      I have below code but still it is displaying Null

    set @sql = 'Insert into #tbl1(SalesPerson,BpName,'
    		Set @Sql = @sql + '[' + CAST(day(@DocDate) AS VARCHAR) +']) values(''' + @SalesPerson + ''',''' + @BpName + ''',' + isnull(CAST(@Amount AS VARCHAR),'') + ')'  


    Tuesday, July 21, 2020 5:30 AM
  • User753101303 posted


    You could print @sql and check #tbl1 to see if you have the proper data. For now it seems the first step is to understand where the problem happens ie :
    - do you try to insert the expected value
    - do you have the expected value in the database
    - or is the problem when you try to show this value

    I'm not sure if you want to show the "Blank" text or a blank input field (which should work out of the box). I always try to handle formatting on the c lient side rather than in the database.

    Tuesday, July 21, 2020 7:58 AM
  • User-797751191 posted

    Hi PatriceSc

       When i execute table it shows Null in fields . I want to show it as blank


    Tuesday, July 21, 2020 8:22 AM
  • User753101303 posted

    What means "execute a table"?

    Do you mean you are seeing NULL when showing a query result in SQL Server Management Studio (SSMS)? This is expected and doesn't prevent an app to show a blank value or something else in an input field if needed.

    The purpose of the database is to store values and most db tools are showing those values in a way that is unambiguous to developers/dbas. It doesn't prevent an application to show a NULL value as blank (which is what should happen basically out of the box) or to use the proper country convention for dates and numbers.

    For now you only looked at values in SSMS ? With most UI framework I would expect this value to be shown as blank by default without having anything to do. You are using WebForms or MVC ? ADO.NET or Entity Framework ?

    Tuesday, July 21, 2020 8:55 AM
  • User475983607 posted

    When i execute table it shows Null in fields . I want to show it as blank

    You are struggling to understand TSQL types.  A DECIMAL type is numeric and cannot contain an empty string.  This is a rule.  

    One option is to query the table and format the results as shown in the example above.

    Tuesday, July 21, 2020 11:01 AM