CODEWITHSUNDEEP
sql-server
Zusee Weekin
Zusee Weekin

Reputation: 1358

How to know default value or stored value

I have a table field which has a default value '0' and users can store any numeric values for it. For my application I need to know the user has stored any value or not.

Is there any way to find out the column in the query returns it's default value or stored value with MSSQL?

Upvotes: 0

Views: 90

Answers (3)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391286

The answer to your question as stated is that a query that returns existing rows from the database will always return the stored value.

A "default value" for a column is only used during inserts:

  1. If the column, during the insert, has a value provided for it, use that value
  2. If the column, during the insert, does not have a value provided for it, use the default value (if one is specified)

Afterwards it is not possible to know if a default value for 0 was used or if the insert provided the value 0 for the column.

So a query will always return the stored value.

Corollary:

  • Changing the default value setting for a field does not change existing rows that was inserted using the old default value. They will keep their current values.
  • Adding a default value for a field that allows null does not set the value of this field for existing rows with null in them to this new default value, again they will keep their existing values (null).

You can monkey around with triggers and stuff to detect that no value was provided, and then update the row with a "default value" as well as set a checkmark or similar in a separate column to indicate that "a default value was provided" but there is no mechanism built into SQL Server to do this for you easily.

Upvotes: 2

Barani
Barani

Reputation: 58

You can try following

use tempdb

 create table #ttt 
(
        c1 int identity not null,
        c2 int not null,
        c3 int default -1,
        c4 as (case when c3 < 0 then c3+1 else c3 end),
        c5 as (case when c3 < 0 then 'DEFAULT VALUE' else 'USER SUPPLIED' end)
)


insert into #ttt values (10, 20)
insert into #ttt values (11, 21)
insert into #ttt (c2) values (20)

select * from #ttt

/*For selections, you can use */
Select C1, C2, C4 from #ttt

Upvotes: 0

Arun Prasad E S
Arun Prasad E S

Reputation: 10115

begin tran

insert into [dbo].[BuyOnlineCartMaster] (OfferAmt)values(0) 
        -- inserting value to some other column, 

select NetAmt from [BuyOnlineCartMaster] where CartID=SCOPE_IDENTITY()  
        -- default value is genereted in the required feild


rollback tran

The select statement gives the default value

make this as a procedure call or something

Upvotes: 0

Related Questions