Conditionally Inserting Default Values

Question

Post reply

Conditionally Inserting Default Values

jxflagg

Hall of Fame

Points: 3817
More actions
January 3, 2005 at 4:42 pm

#162970

I'm writing a stored procedure that, among other things, inserts values into a table. If values have not been provided to the stored procedure, I want to use the table defaults. In other words, I want to do the following (which does not work):
Insert into dbo.Country(
   RegionID,
   [Name],
   Code,
   WatchPoint
)
Values(
   @RegionID,
   @Name,
   @Code,
   isNull(@WatchPoint, default),
)
I know that I can do this with dynamic SQL, but is there another way? I could also default values in the stored procedure, but then I would have to maintain the defaults in two places: on the table and in the procedure. Is there a way I can select the default value for a table column?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

Here is an example of how to set default in a table:

ALTER TABLE MyTable

ADD AddDate smalldatetime NULL

CONSTRAINT AddDateDflt

DEFAULT getdate() WITH VALUES

mom

jxflagg Hall of Fame Points: 3817 More actions · Answer 2

Thanks, but I don't want to set defaults. The defaults are already set. I want to select the defaults.

In the example I gave, I can insert a varaible (@WatchPoint) or the default value with the keyword default. I just can't do it with an isNull function.

As I said, I can do this with dynamic SQL or by setting default values in the stored procedure, but I don't want to do that. I want a function, procedure or statement that lets me select the default values.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

I don't know whether this will be of any help, but here is some code that returns a list of tables/columns and defaults within the current db.

select table_name, column_name, column_default from INFORMATION_SCHEMA.columns

where column_default is not null

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jxflagg Hall of Fame Points: 3817 More actions · Answer 4

Thanks.

I was able to use your code to create a UDF that works for me.

Kemp Davis Ten Centuries Points: 1067 More actions · Answer 5

To do what you wanted in a stored procedure you could try:

DECLARE @default [datatype]

SELECT @default = (SELECT SUBSTRING(column_default, 2, LEN(column_default) - 2) FROM INFORMATION_SCHEMA.columns

WHERE table_name = 'Country' AND column_name = 'WatchPoint')

Insert into dbo.Country(

RegionID,

[Name],

Code,

WatchPoint

)

Values(

@RegionID,

@Name,

@Code,

isNull(@WatchPoint, @default)

)

Kemp