isnull(@myvar, DEFAULT)?
-
I get a bad syntax error when I try to use the default keyword with the isnull() function. What I'd like to do is insert the default value when the variable is null. Anyone know how to do this?
-
What is the default value? That's what you have to put in the function. It can be a column name, another variable of the same or implicitly convertable type, or a literal.
For example, if I wanted to treat all NULLS in a column called MyVal as if they had a value of zero... ISNULL(MyVal,0).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The idea, I thought, was that 'default' will insert whatever the default value of the column is. You're saying I have to hard-code a query for every column? That means if I change the database schema, I have to find/replace every query.
CREATE TABLE #example ( preference VARCHAR(20) NULL DEFAULT ('None') ) INSERT #example ([preference]) VALUES (NULL) INSERT #example ([preference]) VALUES (DEFAULT) INSERT #example ([preference]) VALUES ('Blue') INSERT #example ([preference]) VALUES (ISNULL(@test,DEFAULT)) -
You are right however there is a context issue with trying to use within another SQL Structure. DEFAULT is only understood at the base level and not within functions or statements like ISNULL, COALESCE, CASE, etc. These statements don't understand DEFAULT or how to process within themselves.
-
I see. So is this just Microsoft being dumb, or is it really a SQL standard?
-
I don't know. Might want to post to the MS SQL Boards to find out, I couldn't find anything on the reasoning it was implemented this way. It may be due to the order of processing the query itself.
-
Wait a minute! If you have a default on the column and you don't insert anything into it, then the column will have the default after an insert... why would you insert a null over a default?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
because I want to write a stored proc to do inserts. It means that I have a hard-coded insert statement; that is, the columns are already named and in place. I wanted a way to just insert the default if the value was not sent to the sp.
-
You can code a default for each parameter passed to an SP... from BOL... does that help?
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I was aware of that. However, what I'm trying to do is abstract the proc away from the schema as well as make it more independent. In my view MS SQL does these things poorly. In Oracle, when you define a proc, you can specify the datatype of the variable as being whatever the specified column's datatype is. So, if you change the column on a table, all the procs that use it are adjusted, too. Same idea with the default. I keep expecting to find this is what MS means by "schema-binding", but it never materializes on a T-SQL level.
In the proc below, only record 1 will have a NULL value, and only because it's explicitly specified. If the proc worked the way I wanted it to, I could say EXEC [dbo].[myProc] NULL,NULL and it would insert a new record with a "preference" of "None".
CREATE TABLE #example ( [id] INT NOT NULL IDENTITY(1,1), [preference] VARCHAR(20) NULL DEFAULT ('None') ) INSERT #example ([preference]) VALUES ('Blue') INSERT #example ([preference]) VALUES (NULL) INSERT #example ([preference]) VALUES (DEFAULT) GO CREATE PROCEDURE [dbo].[myProc] @id INT, @pref VARCHAR(20) AS BEGIN IF EXISTS (SELECT * FROM #example WHERE [id]=@id) BEGIN UPDATE #example SET [preference]=COALESCE(@pref,[preference]) WHERE [id]=@id END ELSE BEGIN INSERT #example([preference]) VALUES (COALESCE(@pref,DEFAULT)) END END -
The only solution I can suggest is to use dynamic sql inside your procedure to build the INSERT statement so you place the keyword DEFAULT in the VALUES clause.
During a break, I will try to put together an example.
-
Just curious... can Oracle supply the default as you've stated?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply