Insert DEFAULT when valued in SP is NULL 
Author Message
 Insert DEFAULT when valued in SP is NULL

In an 'insert' stored procedure, with sql7,  how can I indicate to use the
column default (from the table and not set in the SP) when the stored
procedure receives a NULL for that value?

This produces a syntax error but it illustrates what I want to do where I
wrote 'isnull'...


 AS INSERT INTO authors
(value1,value2)
VALUES


)

It seems like a common situation to say "hey, if you get a value passed in,
use it, but otherwise use the default for that column as set in the table
specification".   But I can't find any example of this on the web, old
posts, or BOL.    Isn't this common?  How can I handle this?

Thank you.



Mon, 23 May 2005 05:57:52 GMT
 Insert DEFAULT when valued in SP is NULL

You cannot use DEFAULT keyword inside ISNULL (or in any
other function for that matter). You will have to use the
actual default value, if you are using ISNULL/COALESCE/
CASE expression.

--
- Anith
(Please respond only to newsgroups)



Mon, 23 May 2005 06:17:16 GMT
 Insert DEFAULT when valued in SP is NULL
Something like this:

CREATE PROCEDURE Test

)
AS


BEGIN
INSERT INTO TABLE1 (test)
VALUES (DEFAULT)
END
ELSE
BEGIN
INSERT INTO TABLE1 (test)

END



Mon, 23 May 2005 06:36:32 GMT
 Insert DEFAULT when valued in SP is NULL
I was just trying to demonstrate what I wanted to accomplish.  The real
question is how to handle this situation of conditionally using the column
default (from the table and not set in the SP) when the stored procedure
receives a NULL for that value.  However, if someone wants to say 'you
should always store you defaults in the sp'  then I'd listen, but it
otherwise seems that having a centralized location for storing defaults
(i.e. the table) is better than having defaults scattered amongst possibly
many stored procedures.


Quote:
> You cannot use DEFAULT keyword inside ISNULL (or in any
> other function for that matter). You will have to use the
> actual default value, if you are using ISNULL/COALESCE/
> CASE expression.

> --
> - Anith
> (Please respond only to newsgroups)



Mon, 23 May 2005 07:06:05 GMT
 Insert DEFAULT when valued in SP is NULL
I appreciate the response, but it seems that would be overwhelming for 20
fields.

If most people prefer to store backup defaults in their SP itself, then how
would they write the code that would say "pass default of 'xyz' if received
a null" ??


Quote:
> Something like this:

> CREATE PROCEDURE Test
> (

> )
> AS


> BEGIN
> INSERT INTO TABLE1 (test)
> VALUES (DEFAULT)
> END
> ELSE
> BEGIN
> INSERT INTO TABLE1 (test)

> END



Mon, 23 May 2005 07:13:12 GMT
 Insert DEFAULT when valued in SP is NULL
I would suggest setting the default values for your parameters. It's much
easier and you know exactly what the stored procedure has to deal with.
e.g.

as
...

--
-oj
http://www.rac4sql.net


Quote:
> I was just trying to demonstrate what I wanted to accomplish.  The real
> question is how to handle this situation of conditionally using the column
> default (from the table and not set in the SP) when the stored procedure
> receives a NULL for that value.  However, if someone wants to say 'you
> should always store you defaults in the sp'  then I'd listen, but it
> otherwise seems that having a centralized location for storing defaults
> (i.e. the table) is better than having defaults scattered amongst possibly
> many stored procedures.



> > You cannot use DEFAULT keyword inside ISNULL (or in any
> > other function for that matter). You will have to use the
> > actual default value, if you are using ISNULL/COALESCE/
> > CASE expression.

> > --
> > - Anith
> > (Please respond only to newsgroups)



Mon, 23 May 2005 07:10:21 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Insert sp and Default column values

2. default value / allows null / insert

3. Generic way to insert NULL as the default value

4. default value / null insert

5. HOWTO: Declare table field having Default Value - NULL or NOT NULL

6. Use ISNULL to set the value to the default value when NULL

7. inserting null values into not null column in Server 7.0

8. simulating INSERT return values with default values

9. simulating INSERT return values with default values of sequences

10. Inserting Null values using Bulk Insert

11. Null Values - WHY am I getting this error?

12. Finding default values for an SP


 
Powered by phpBB® Forum Software