How to use "DEFAULT" when a field is null in an SP 2

[jonbatts]

I'm passing myVariable into a stored procedure. Inside my stored procedure I want to store myVariable into MyTable.myField. MyTable.myField has a default value. If myVariable is null, I want to use myField's DEFAULT value. The following code doesn't work

UPDATE MyTable SET myField = ISNULL(myVariable, DEFAULT) WHERE myID = 1

What's the correct syntax to store myVariable (OR myField's default value if myVariable is null) into MyTable.myField? Thanks, and have a great day?

 

[gmmastros]

You could try...

UPDATE MyTable 
SET    myField = ISNULL(myVariable, [!]myField[/!]) 
WHERE  myID = 1

If myVariable is NULL, then you will be setting myField = myField (essentially unchanged). However, if myVariable is not null, then myField will be set to its value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jonbatts]

I don't actually want to leave it unchanged, I want it to set to the default value for the field.

 

[monksnake]

Do you have to set the default for many different columns?? If you don't then it would seem easiest just to hardcode the default values in the ISNULL statement.

ISNULL(myVariable, 'string')

As long as the same exact columns are using this SP, this would work just fine, though if not, I can see your dilemma.

<.

 

[SQLSister]

If the field has a default value, then don't update it. Use if to determine if the update statment needs to run or not.

Questions about posting. See faq183-874

 

[jonbatts]

I've got a form where Users enter data that has default values (which I've put as Defaults in the database). Say Field1 has a default of 'The Three Amigos'. When User1 comes to a page to change Field1 he sees the default. He can change it to 'Corky Romano'. Later, if he comes back and changes it to an empty string, I want it to return to 'The Three Amigos'. I'd like to only have to store that default in one place (the default value of the field), so hard-coding 'The Three Amigos' in the ISNULL statement isn't a good solution for me. Any other ideas? Can this be done? Thanks to all of you for your help.

 

[gmmastros]

The question really is... How do I know what the default value is for a column???

I'm working on this and should have a solution soon.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I can't help but thinking that there must be a better way, but this is the best I could come up with.

Declare @DefaultValue VarChar(1000)

Select @DefaultValue = SubString(Text, 3, len(text)-4)
From   Sysobjects
       Inner Join SysComments
         On sysobjects.id = syscomments.id
Where  xtype = 'D'
       And Object_Name(parent_obj) = '[!]MyTable[/!]'

UPDATE MyTable SET myField = ISNULL(myVariable, @DefaultValue) WHERE myID = 1

Notice this bit of 'hokeyness'.

Select @DefaultValue = SubString(Text, 3, len(text)-4)

If the data type is varchar, then the 'text' column will look like... ('Three Amigos')

If the data type is int, the text column will look like...
(25)


This is 'minimially' tested. You need to make sure that it works in your situation. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Hold on a minute. While the above query will work when only 1 column has a default, it could provide the wrong results when there are more than 1 columns in the table with a default.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

This should be a little better.

Declare @DefaultValue VarChar(1000)

Select @DefaultValue = Text
From   Sysobjects
       Inner Join syscolumns
         On  sysobjects.parent_obj = syscolumns.id
         and sysobjects.info = syscolumns.colid
       Inner Join SysComments
         On sysobjects.id = syscomments.id
Where  sysobjects.xtype = 'D'
       And Object_Name(parent_obj) = '[!]MyTable[/!]'
       And syscolumns.name = '[!]MyField[/!]'

UPDATE MyTable SET myField = ISNULL(myVariable, @DefaultValue) WHERE myID = 1

Hope it helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I found another way, but it's probably not much different.

Declare @DefaultValue VarChar(1000)

Select @DefaultValue = Text
From   Sysobjects
       Inner Join SysComments
         On sysobjects.id = syscomments.id
Where  sysobjects.xtype = 'D'
       And Object_Name(parent_obj) = '[!]MyTable[/!]'
       And col_name(parent_obj, info) = '[!]MyField[/!]'

UPDATE MyTable SET myField = ISNULL(myVariable, @DefaultValue) WHERE myID = 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

George you can also use this

select column_default from information_schema.columns

select column_default from information_schema.columns

Declare @DefaultValue VarChar(1000)

Select @DefaultValue = column_default
From   information_schema.columns
Where  table_name = 'MyTable'
and    column_name = 'MyColumn' --sorry had to ;-)

UPDATE MyTable SET myField = ISNULL(myVariable, @DefaultValue) WHERE myID = 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[AlexCuse]

and column_name = 'MyColumn' --sorry had to ;-)

priceless!

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

well Alex that is because we all know that
Rows are not records; fields are not columns; tables are not files!!

just ask Celko ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Well.... It did seem a lot harder than it should have been. I had actually checked information_schema.columns, but didn't see the column_default. I suppose I should have scrolled to the right?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philhege]

Denis, be careful when you invoke Celko; you could inadvertently bring his wrath into the Tek-Tips realm ;^)

/Mere SQL mortals quake

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[sunila7]

What about something like this.

DECLARE @myV varchar(10)
IF (@myV IS NULL)
BEGIN
	UPDATE MyTable SET myField = DEFAULT WHERE myID = 1
 END 
ELSE
BEGIN
	UPDATE MyTable SET myField = @MyV
	WHERE myID = 1
END

Sunil

 

[jonbatts]

I'm actually updating a table with 65 fields (columns), 63 of which have defaults which I want to be used if the user doesn't enter anything. It seems to me that the following is a bit cumbersome.

UPDATE myTable
SET field1 = ISNULL(@var1, (
    SELECT COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable' AND COLUMN_NAME = 'field1'),
field2 = ISNULL(@var2, (
    SELECT COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable' AND COLUMN_NAME = 'field2'),
...

Since it's so easy to say [tt]UPDATE myTable SET field1 = DEFAULT WHERE myID = 1[/tt], I assumed there would be a simple syntax for my situation where I only want it to default if it's null. I guess I'll have to decide whether I want convoluted sql, or just put my default values in more than one place; i.e. the table and the SP via [tt]ISNULL(field1, 'default')[/tt]
I really appreciate all your alls help. Even though I don't know your inside jokes about Celko or his wrath (sounds like a bad mother-shut yo' mouth. I'm only talkin' about Celko).