September 23, 2014 at 9:16 am
Dear Friends,
I have a Nullable columns. I want the value to be zero if NULL is passed.
I have a solution but felt it is awkward, that
1) having a view for every such table,
2) using ISNULL for needed columns in that view
3) using those view for inserts instead of using tables directly.
This way i can reduce the number of places to use ISNULL in all my SPs.
can this be a solution, or do we have any better one.
Always appreciating your help.
Note: I dont want to make it to NOT NULL, as my application impact will be high.
And Default constraint does not help here.
September 23, 2014 at 11:21 am
I'm assuming that you mean when you pass NULL to an insert/update statement?
There are a couple of things I think would work, 1 that impacts your application and 1 that doesn't beyond possible performance issues:
1. Use stored procedures for inserts and updates and on those parameters that are passed in as NULL for columns you want 0 replace the passed in NULL with a 0.
2. Put an INSTEAD OF trigger on your tables that check for NULL's being inserted into those columns and replace with 0.
In reality the best solution is to set the columns to NOT NULL and require the application to pass a 0, but you ruled that out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2014 at 11:59 pm
Jack Corbett (9/23/2014)
I'm assuming that you mean when you pass NULL to an insert/update statement?There are a couple of things I think would work, 1 that impacts your application and 1 that doesn't beyond possible performance issues:
1. Use stored procedures for inserts and updates and on those parameters that are passed in as NULL for columns you want 0 replace the passed in NULL with a 0.
2. Put an INSTEAD OF trigger on your tables that check for NULL's being inserted into those columns and replace with 0.
In reality the best solution is to set the columns to NOT NULL and require the application to pass a 0, but you ruled that out.
Thanks for your reply.
reg. your point 1: having VIEW for each table for insert and update (as my proposed-solution) would be better i think, instead of stored procedure where you have to pass parameters
reg. your point 2: as you mentioned, i fear of performance issue here, as why to fire the TRIGGER every time. here too, i feel having a view is better.
Thanks again for your time Jack.
September 24, 2014 at 8:55 am
SQL Mad Rafi (9/23/2014)
Jack Corbett (9/23/2014)
I'm assuming that you mean when you pass NULL to an insert/update statement?There are a couple of things I think would work, 1 that impacts your application and 1 that doesn't beyond possible performance issues:
1. Use stored procedures for inserts and updates and on those parameters that are passed in as NULL for columns you want 0 replace the passed in NULL with a 0.
2. Put an INSTEAD OF trigger on your tables that check for NULL's being inserted into those columns and replace with 0.
In reality the best solution is to set the columns to NOT NULL and require the application to pass a 0, but you ruled that out.
Thanks for your reply.
reg. your point 1: having VIEW for each table for insert and update (as my proposed-solution) would be better i think, instead of stored procedure where you have to pass parameters
reg. your point 2: as you mentioned, i fear of performance issue here, as why to fire the TRIGGER every time. here too, i feel having a view is better.
Thanks again for your time Jack.
Assuming I understand how you were planning to use the view (i.e. land the data as is somewhere temporary, then use a view as the SELECT part of the insert into the long-term table), the logic will end up being the same. Frankly the trigger might be less of a performance issue since you're performing just one insert and not 2.
Otherwise - a properly design dynamic SQL proc to create the insert on the fly would also help, since you could leverage the DEFAULT values in the table def.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 24, 2014 at 9:10 am
Are you saying you've got a nullable column, but you don't want to store any NULLs in it, or are you saying you've got a nullable column that does have NULLs in, but you want to display them as 0s?
John
September 24, 2014 at 10:03 am
Matt Miller (#4) (9/24/2014)
Assuming I understand how you were planning to use the view (i.e. land the data as is somewhere temporary, then use a view as the SELECT part of the insert into the long-term table), the logic will end up being the same. Frankly the trigger might be less of a performance issue since you're performing just one insert and not 2.Otherwise - a properly design dynamic SQL proc to create the insert on the fly would also help, since you could leverage the DEFAULT values in the table def.
I would agree with Matt. Especially if you do an INSTEAD OF trigger and not the default AFTER trigger. With an AFTER trigger changes have been logged, so any change to values would mean 2 writes to the log, and would need to roll back if there is a problem. An INSTEAD OF trigger replaces the initial action with whatever you put in the trigger so nothing is logged until the trigger completes, so replacement of values now is before the action so only 1 log write takes place. This still isn't as efficient as just providing the values to the insert/update either from the application or through a stored procedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2014 at 1:46 am
After your explanation of logging, i feel having an INSTEAD OF Trigger is a better solution here.
thx Dears.
September 28, 2014 at 12:58 am
But, why should it still say
1 row affected
1 row affected
a two times, INSTEAD OF does a single INSERT correct?
September 29, 2014 at 3:41 am
Not sure if I understood your requirements but if you just want to pass 0 to columns while inserting/updating data, don't you think it would be easier to ALTER the table/tables to have DEFAULT value to 0 for those column/columns?
September 29, 2014 at 7:21 am
I thought there were issues with using a DEFAULT constraint, as it will only kick in on INSERT if the column isn't explicitly named, or you use 'DEFAULT' clause on the INSERT values.
eg
CREATE TABLE #T
(
Id INT
,DefaultCol INT DEFAULT(0)
)
INSERT INTO #T
(id,DefaultCol)
VALUES (1,NULL)
INSERT INTO #T
(Id)
VALUES (2)
INSERT INTO #T
(Id,DefaultCol)
VALUES (3,DEFAULT)
Select * from #T
Results
1,NULL
2,0
3,0
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 30, 2014 at 1:18 am
I believe the CASE function with INSERT & UPDATE will still meet the OP's requirements..
Cheers
October 1, 2014 at 2:00 am
ishitvora1122 (9/30/2014)
I believe the CASE function with INSERT & UPDATE will still meet the OP's requirements..Cheers
From the initial post:
I have a Nullable columns. I want the value to be zero if NULL is passed.
You have offered a solution which does not match requirements.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2014 at 10:54 am
If you allow nullable columns, but wnat to 'show' a '0', you can use ISNULL(col1,0).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply