June 8, 2011 at 6:18 am
I am having an issue with blank values in smalldatetime fields.
The problem being that a stored procedure inserts data into a table, when the data inserts a blank field into a smalldatetime error295 is displayed, what I want is when the field is blank the column is set to Null. I have looked at the replace command but am not sure of the syntax to set the column to Null the column is set the allow Null values.
REPLACE(@FIELD24,'''','NULL')
Any help would be much appreciated
June 8, 2011 at 6:52 am
The function you need is "NullIf".
select NullIf(@Field24, '')
If the first value equals the second value, it returns a null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2011 at 8:49 am
thanks for your reply but i can't seem to fet the Nullif to work
' SELECT '''+ @FIELD1+''', ''' +@FIELD2+''', ' +CAST(@FIELD3 AS VARCHAR)+', ''' +@FIELD4+''', ''' +nullif(@FIELD5,'0')+''', ''' +@FIELD6+''', ''' +@FIELD7+''', ''' +@FIELD8+''', ''' +@FIELD9+''', ''' +@FIELD10+''', ''' +@FIELD11+''', ''' +@FIELD12+''', ''' +@FIELD13+''', ''' +@FIELD14+''', ''' +@FIELD15+''', ''' +@FIELD16+''', ''' +@FIELD17+''', ''' +@FIELD18+''', ''' +@FIELD19+''', ''' +@FIELD20+''''
🙂
June 9, 2011 at 7:17 am
Per your first post, the value would be "blank", per the code you just posted, it will be "0", in the cases where you want it to be NULL instead. Or am I missing something here?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2011 at 3:13 am
I will explain a bit further.
SET @INSERT_QUERY = @INSERT_QUERY +'[New_RECORD]'+ @INSERT_COLS1 +
' SELECT ''' +@FIELD13+''', ''' +@FIELD14+''', ''' +nullif(@FIELD15,0)+''', ''' +@FIELD16+''', ''' +@FIELD20+''''
I am trying to insert into a eg_table from a master table
Field15 in the master table is a data type of nvarchar, field15 in eg_table is a smalldatetime.
At the moment it only inserts into eg_table field15 if the correct format ie dd/mm/yyyy.
When the record in the master table field15 is 0 which means no date was supplied it can't insert it into the eg_table because its not the correct format therefore i want a Null value inserted into the eg_table field15 instead.
therefore in the statement above ''' +nullif(@FIELD15,0)+''' when @field15 is 0 a null value is inserted .
I am not sure whether there is a problem with the syntax or whether this is the best wayof doing it.
🙂
June 10, 2011 at 1:04 pm
NullIf isn't the way to go for what you're doing. It will render your whole string null.
What you need is a Case statement. Check if the value of the column = 0. If so, then add 'NULL' as a string to your insert statement.
Something like:
SET @INSERT_QUERY = @INSERT_QUERY +'[New_RECORD]'+ @INSERT_COLS1 +
' SELECT ''' +@FIELD13+''', ''' +@FIELD14+''', ''' + case @FIELD15 when 0 then 'NULL' else @Field15 end
+''', ''' +@FIELD16+''', ''' +@FIELD20+''''
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 13, 2011 at 7:37 am
Thanks for your reply, but I am still having problems inserting NULL into field15 when field15 is 0, is it because i am tying to insert it into a smalldate datatype, I put a print statement to see the results of the insert statement and it looks like it is trying to insert Null value but it seems to skip that insert and moves onto one where there is an actual date.
This is getting very frustrating:crazy:
June 13, 2011 at 7:43 am
June 13, 2011 at 8:11 pm
I think maybe GSquared's reply should say:
case @FIELD15 when '0' then NULL else @Field15 end
rather than
case @FIELD15 when 0 then 'NULL' else @Field15 end
Are you sure your Master table records always have '0' in the nvarchar Field15 and not sometimes blank characters or an empty string or null?
Are you sure your eg_table allows NULL in smalldatetime Field15?
June 14, 2011 at 2:31 am
I think i have found the problem but not sure how to solve it, I am using SQL 2005 and when I insert a row with a null value it doesn't seem to process that line, it looks like it skips that line, does anybody have a solution.
June 14, 2011 at 12:53 pm
Any Null in the string will render the string null.
Try this:
select 'Hello ' + Null + 'world';
That's why I enclosed "NULL" in single-quotes in my sample solution, to make it part of the string instead of it being an actual Null. Nulls often create this kind of problem in dynamic SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2011 at 4:45 am
There is also a possibility that your target column doesn't allow NULL.
Also, you might want to consider using a Stored Procedure to accomplish the insert, if you are doing the inserts one at a time. Moving the code to a stored proc could give you a little better control within the database.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply