Default Value Does Not Work On Insert

I'm totally flabbergasted,
In Enterprise Manager, the column will fill with the Default Value when no
data is entered as it should.
However, in my Stored Procedure, it won't.
Table:
Col1 uniqueidentifier pkey not null default newid()
Col2 tinyint not null default 20
Procedure:
@.Col1 UNIQUEIDENTIFIER = NULL,
@.Col2 TINYINT = NULL
SET @.Col1 = NEWID() /*Set to use later on*/
INSERT INTO dbo.Table1
(Col1, Col2)
VALUES (@.Col1, @.Col2)
If Col2 column is described as NOT null, it errs saying it can't insert null
into the col.
I would think that once it saw null, it would replace it with default.
If Col2 column is described as null, it would fill it with null and NOT
default.
If I replace @.Col2 with default, the default value is filled in.
Am I missing something here? I could have sworn I've done it before and it's
blowing my mind why it won't work.
Appreciating any assistance,
NathanSQL Server doesn't replace default with null. If you specify NULL, then it i
s because you want to
have NULL. Specifying NOT NULL in CREATE TABLE doesn't change this.
EM might do some strange smarts here, but EM does a lot of strange things.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:4BDDED11-1A72-45A6-B87D-ACD0F863C4B8@.microsoft.com...
> I'm totally flabbergasted,
> In Enterprise Manager, the column will fill with the Default Value when no
> data is entered as it should.
> However, in my Stored Procedure, it won't.
> Table:
> Col1 uniqueidentifier pkey not null default newid()
> Col2 tinyint not null default 20
> Procedure:
> @.Col1 UNIQUEIDENTIFIER = NULL,
> @.Col2 TINYINT = NULL
> SET @.Col1 = NEWID() /*Set to use later on*/
> INSERT INTO dbo.Table1
> (Col1, Col2)
> VALUES (@.Col1, @.Col2)
> If Col2 column is described as NOT null, it errs saying it can't insert nu
ll
> into the col.
> I would think that once it saw null, it would replace it with default.
> If Col2 column is described as null, it would fill it with null and NOT
> default.
> If I replace @.Col2 with default, the default value is filled in.
> Am I missing something here? I could have sworn I've done it before and it
's
> blowing my mind why it won't work.
> Appreciating any assistance,
> Nathan|||A little confusion here.
I want default to be used when "nothing" has been given.
In the SP, I have = Null so my app doesn't have to send anything.
If I don't have = Null in the SP, then my app is required to send something
and that may or may not be dbnull.value or "nothing".
Whatever the case, when I have nothing to give from my app, I want the DB to
set the value to the Default val.
I was just switching the Not Null for the Column for debugging purposes. I
thought it should have set the record cell to default value regardless of th
e
Null or Not Null.
I hope this is helping to clear up my situation and hope you have something
else for me.
Thanks for your input so far.
Nathan
"Tibor Karaszi" wrote:

> SQL Server doesn't replace default with null. If you specify NULL, then it
is because you want to
> have NULL. Specifying NOT NULL in CREATE TABLE doesn't change this.
> EM might do some strange smarts here, but EM does a lot of strange things.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:4BDDED11-1A72-45A6-B87D-ACD0F863C4B8@.microsoft.com...
>
>|||If you want SQL Server to use the default value on insert, use the
DEFAULT keyword or do not specify the column at all:
INSERT INTO dbo.Table1 (Col1, Col2) VALUES (@.Col1, DEFAULT)
or
INSERT INTO dbo.Table1 (Col1) VALUES (@.Col1)
Razvan|||See Razvan's post. He posted the two ways for which SQ!L Server will apply a
default values. I.e.,
you probably have to check parameters values in the procedure and execute th
e inserts conditionally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:3B533344-91F5-4EF8-8BA6-637E8420DD44@.microsoft.com...
>A little confusion here.
> I want default to be used when "nothing" has been given.
> In the SP, I have = Null so my app doesn't have to send anything.
> If I don't have = Null in the SP, then my app is required to send somethin
g
> and that may or may not be dbnull.value or "nothing".
> Whatever the case, when I have nothing to give from my app, I want the DB
to
> set the value to the Default val.
> I was just switching the Not Null for the Column for debugging purposes. I
> thought it should have set the record cell to default value regardless of
the
> Null or Not Null.
> I hope this is helping to clear up my situation and hope you have somethin
g
> else for me.
> Thanks for your input so far.
> Nathan
> "Tibor Karaszi" wrote:
>|||OK, I think I will have to use a trigger for this (first timer here).
I thought I could conditionally set the value if it was Null to Default, but
it doesn't like me using the word Default in a condition (isnull).
Thanks for the help you two,
Nathan
"Razvan Socol" wrote:

> If you want SQL Server to use the default value on insert, use the
> DEFAULT keyword or do not specify the column at all:
> INSERT INTO dbo.Table1 (Col1, Col2) VALUES (@.Col1, DEFAULT)
> or
> INSERT INTO dbo.Table1 (Col1) VALUES (@.Col1)
> Razvan
>|||On Mon, 25 Apr 2005 10:27:02 -0700, Nathan wrote:

>OK, I think I will have to use a trigger for this (first timer here).
>I thought I could conditionally set the value if it was Null to Default, bu
t
>it doesn't like me using the word Default in a condition (isnull).
>Thanks for the help you two,
>Nathan
Hi Nathan,
No need to use a trigger here! Just change the code in your stored
procedure to:
@.Col1 UNIQUEIDENTIFIER = NULL,
@.Col2 TINYINT = NULL
SET @.Col1 = NEWID() /*Set to use later on*/
IF @.Col2 IS NULL
INSERT INTO dbo.Table1 (Col1, Col2)
VALUES (@.Col1, DEFAULT)
ELSE
INSERT INTO dbo.Table1 (Col1, Col2)
VALUES (@.Col1, @.Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
If anything, I really would have liked to use an embedded conditional
statement, but don't seem to be able to.
For simplicity-sake, I only used two columns here, but there are several,
most having the possibility of being a value or needing to use a default
value.
So the combinations/permutations would just end up making it a mess.
I know, I could just set the default value (replacing =Null), but I want it
tightly-knitted with the database itself.
It seems that the trigger is the solution I'm looking for. At least there
are options. I always say, you can find at least 5 ways to copy and paste.
Thanks for the input,
Nathan
"Hugo Kornelis" wrote:

> On Mon, 25 Apr 2005 10:27:02 -0700, Nathan wrote:
>
> Hi Nathan,
> No need to use a trigger here! Just change the code in your stored
> procedure to:
> @.Col1 UNIQUEIDENTIFIER = NULL,
> @.Col2 TINYINT = NULL
> SET @.Col1 = NEWID() /*Set to use later on*/
> IF @.Col2 IS NULL
> INSERT INTO dbo.Table1 (Col1, Col2)
> VALUES (@.Col1, DEFAULT)
> ELSE
> INSERT INTO dbo.Table1 (Col1, Col2)
> VALUES (@.Col1, @.Col2)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Mon, 25 Apr 2005 19:49:18 -0700, Nathan wrote:

>Hi Hugo,
>If anything, I really would have liked to use an embedded conditional
>statement, but don't seem to be able to.
>For simplicity-sake, I only used two columns here, but there are several,
>most having the possibility of being a value or needing to use a default
>value.
>So the combinations/permutations would just end up making it a mess.
>I know, I could just set the default value (replacing =Null), but I want it
>tightly-knitted with the database itself.
>It seems that the trigger is the solution I'm looking for. At least there
>are options. I always say, you can find at least 5 ways to copy and paste.
Hi Nathan,
I agree that the IF ... ELSE tree would grow quickly is the same logic has
to be applied to many columns. Writing triggers is one way around this.
But I doubt that it is the best way.
You already use a stored procedure to insert the data. Why not include the
default value in the code of the stored procedure? You say that you don't
want to do this because you want it tightly-knitted with the DB, but then
you want to code the default value in a trigger. How is a trigger more
tightly-knitted with the DB than a stored procedure?
CREATE PROC InsertIt
(@.KeyValue int,
@.Data1 varchar(20) = NULL,
@.Data2 datetime = NULL,
@.Date3 int = NULL)
INSERT INTO YourTable (KeyCol, Data1, Data2, Data3)
SELECT @.KeyValue,
COALESCE (@.Data1, 'Default value'),
COALESCE (@.Data2, CURRENT_TIMESTAMP),
COALESCE (@.Data3, 100)
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually, I was starting on the TR just to get some experience with it, but
figured my only app using the DB would work just fine setting the SP
parameter defaults.
I tend to think of future possibilities such as another app that will use
the DB, but not want to make use of that SP and may require another SP, and
then needing to remember to adjust the defaults for the parameters in each
SP. If I just had a single trigger, I wouldn't have to worry about default
parameters in different SPs.
But like I just said, I dropped that thought and will just stick with the
"here and now" of the single SP.
Just my way of thinking is all.
Nathan
"Hugo Kornelis" wrote:

> On Mon, 25 Apr 2005 19:49:18 -0700, Nathan wrote:
>
> Hi Nathan,
> I agree that the IF ... ELSE tree would grow quickly is the same logic has
> to be applied to many columns. Writing triggers is one way around this.
> But I doubt that it is the best way.
> You already use a stored procedure to insert the data. Why not include the
> default value in the code of the stored procedure? You say that you don't
> want to do this because you want it tightly-knitted with the DB, but then
> you want to code the default value in a trigger. How is a trigger more
> tightly-knitted with the DB than a stored procedure?
> CREATE PROC InsertIt
> (@.KeyValue int,
> @.Data1 varchar(20) = NULL,
> @.Data2 datetime = NULL,
> @.Date3 int = NULL)
> INSERT INTO YourTable (KeyCol, Data1, Data2, Data3)
> SELECT @.KeyValue,
> COALESCE (@.Data1, 'Default value'),
> COALESCE (@.Data2, CURRENT_TIMESTAMP),
> COALESCE (@.Data3, 100)
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>