Solved

INSERT NULL IN SMALLDATETIME DATATYPE

Posted on 2002-05-08
7
813 Views
Last Modified: 2012-06-21
Hi,

I have a stored procedure that insert a record into a table.  I am passing Call_Log_Date as text.  When is blank, I like to get updated with null. If a date, then updated with date.  Call_Log_Date is a SmallDateTime field.  I know the problem but not sure how to solve. I think the single qoute around the @vCall_Log_Date.  It works fine when I pass date, but When I pass blank, it put default value '01/01/1900' which I don't wont.

CREATE PROCEDURE up_uwc_QuitLine_M_AddNewRecords_1

@Client_ID  nvarchar(5),
@Call_ID  nvarchar(4),
@Followup_ID  nvarchar(4),
@Call_Log_Count  nvarchar(4),
@Call_Log_Date  nvarchar(20),
@Call_Log_Time  nvarchar(50),
@Call_Log_Complete  nvarchar(3),
@Call_Log_Message_Left nvarchar(3)

as

DECLARE @stsql varchar(1000)
DECLARE @vPrint varchar(1000)
DECLARE @vGroupNum  nvarchar(10)
DECLARE @vCall_Log_Date nvarchar(20)

--set @stsql = ''

if @Call_Log_Date = ''
begin
     set @vCall_Log_Date = null
end
else
begin
     set @vCall_Log_Date = @Call_Log_Date
     --set @vCall_Log_Date = convert(DATETIME,  @Call_Log_Date )
end

exec  ('INSERT INTO QuitLine_Call_Log (Client_ID, Call_ID, Followup_ID,
    Call_Log_Count, Call_Log_Date, Call_Log_Time, Call_Log_Complete, Call_Log_Message_Left)
VALUES( '  
+ @Client_ID
+',  ' + @Call_ID
+',  ' + @Followup_ID
+',  ' + @Call_Log_Count
+',  ''' + @vCall_Log_Date
+''', ''' + @Call_Log_Time
+''', ''' + @Call_Log_Complete
+''', ''' + @Call_Log_Message_Left
+ ''')' )
0
Comment
Question by:Salim_sam
  • 4
  • 3
7 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 75 total points
ID: 6997519
You can make sure that if the date contain just spaces it still gets overridden by changing your if to this:

IF REPLACE(@Call_Log_Date,' ','') = ''

Also, make sure that the definition of the column in the table allows NULL.

By the way, I don't think you have to use an EXEC here to do your INSERT; something like this should work fine too:

INSERT INTO QuitLine_Call_Log (Client_ID, Call_ID, Followup_ID,
   Call_Log_Count, Call_Log_Date, Call_Log_Time, Call_Log_Complete, Call_Log_Message_Left)
VALUES(@Client_ID, @Call_ID, @Followup_ID, @Call_Log_Count,
@vCall_Log_Date, @Call_Log_Time, @Call_Log_Complete,
@Call_Log_Message_Left)
0
 

Author Comment

by:Salim_sam
ID: 6998723
Hi ScottPletcher,


Thank you very much,

It is working.

Can you explain If there any downfall to the exec query
Is it a good practice? Or where would you use something like it?
exec('select...')



Thank you
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6998863
In general, the EXEC will not perform as well (SQL cannot pre-parse the query, it has to wait until run time to even check the syntax of the query).  Also, EXEC plans are not saved in cache.  If this runs multiple times, SQL will have to check the syntax and convert it into executable instructions separately every time it runs.  

Finally, EXEC requires that the specific user running the query has insert permission directly on their id, not from a role.  If you use roles to manage security, it can be a pain to have to also keep separate individual permissions because of EXEC.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Salim_sam
ID: 6998983
Sorry for extending,

I guess for this query I have to use exec(...)
It is not working as is now.  Then of course I will get a date (NULL) error problem when I change it.  Any help for this one.  I appreciated a lot. Thank you. Last one on this points.

Problem:  Declare variable error
@vCallOrFollowup
@vCallOrFollowupField


DECLARE @vCallOrFollowup  varchar(30)
DECLARE @vCallOrFollowupField  varchar(30)
DECLARE @vCallOrFollowupValue  varchar(30)

IF @Followup_ID = 0
BEGIN
     SET @vCallOrFollowup = 'QuitLine_Call'
     SET @vCallOrFollowupValue = @Call_ID
     SET @vCallOrFollowupField = 'Call_ID'
END
ELSE
BEGIN
     SET @vCallOrFollowup = 'QuitLine_Followup'
     SET @vCallOrFollowupValue = @Followup_ID
     SET @vCallOrFollowupField = 'Followup_ID'
END


INSERT INTO  @vCallOrFollowup   (Client_ID, @vCallOrFollowupField, Counselor_First,
    Counselor_Last, Counselor_Ext, Call_StartTime,
    Call_EndTime, Num_Days_Smoking, Smoking_Per_Day, Started_Smoking_Age, Tried_Quit_Smoking,
    Longest_Abstinence_Smoking, Client_Best_Describes,
    Education, Marital_Status, Family_People_Live_InHome,
    Family_Under18_Live_InHome, What_Age1, What_Age2,
    What_Age3, What_Age4, Smoking_Allowed_InHome,
    Currently_Pregnant, Pregnant_estimated_due_date, Pregnant_Smoking_Quitdate,
    Next_Call_Date, Next_Call_Time, Follow_Up_1, Follow_Up_2, Follow_Up_3,
    Ok_Contact_Again, On_Leave_Message, Case_Notes,
    Call2_Smoked_Past_7_Days,
    Call2_Smoked_Even_Puff_Since_Lasttime,
    Call2_Elapsed_Between_Days, Receiving_Additional_Services)
VALUES(@Client_ID, @vCallOrFollowupValue, @Counselor_First, @Counselor_Last, @Counselor_Ext, @Call_StartTime,
@Call_EndTime, @Num_Days_Smoking, @Smoking_Per_Day, @Started_Smoking_Age, @Tried_Quit_Smoking,
@Longest_Abstinence_Smoking, @Client_Best_Describes, @Education, @Marital_Status, @Family_People_Live_InHome,
@Family_Under18_Live_InHome, @What_Age1, @What_Age2, @What_Age3, @What_Age4, @Smoking_Allowed_InHome,
@Currently_Pregnant, @Pregnant_estimated_due_date, @Pregnant_Smoking_Quitdate, @Next_Call_Date, @Next_Call_Time,
@Follow_Up_1, @Follow_Up_2, @Follow_Up_3, @Ok_Contact_Again, @On_Leave_Message, @Case_Notes,
@Call2_Smoked_Past_7_Days, @Call2_Smoked_Even_Puff_Since_Lasttime, @Call2_Elapsed_Between_Days, @Receiving_Additional_Services)




0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6999476
You can still avoid an EXEC using something similar to:

IF @Followup_ID = 0
BEGIN
    SET @vCallValue = @Call_ID
    SET @vFollowupValue = ''
END --IF
ELSE
BEGIN
    SET @vCallValue = ''
    SET @vFollowupValue = @Followup_ID
END --ELSE

INSERT INTO  @vCallOrFollowup   (Client_ID, Call_ID, Followup_ID, Counselor_First,
...
VALUES (@Client_ID, @vCallValue, @vFollowupValue, @Counselor_First,
...
   



0
 

Author Comment

by:Salim_sam
ID: 6999672
Hi,

@vCallOrFollowup - it does not line it
it can have Quitline_call or QuitLine_Followup - Table name

If Quitline_call - Only Call_id - No followup_id
If QuitLine_Followup - Only Followup_If - No Call_id

Below line is the way I have, but it keeps giving me error.
INSERT INTO  @vCallOrFollowup   (Client_ID, @vCallOrFollowupField, Counselor_First,


My Procedure:
CREATE PROCEDURE up_uwc_QuitLine_M_AddNewRecords

@Client_ID  nvarchar(5),
@Call_ID  nvarchar(4),
@Followup_ID  nvarchar(4),
@Counselor_First  nvarchar(50),
@Counselor_Last  nvarchar(50),
@Counselor_Ext  nvarchar(50),
@Call_StartTime  nvarchar(20),
@Call_EndTime  nvarchar(20),
@Num_Days_Smoking  nvarchar(50),
@Smoking_Per_Day  nvarchar(50),
@Started_Smoking_Age  nvarchar(50),
@Tried_Quit_Smoking  nvarchar(50),
@Longest_Abstinence_Smoking  nvarchar(50),
@Client_Best_Describes  nvarchar(200),
@Education  nvarchar(200),
@Marital_Status  nvarchar(50),
@Family_People_Live_InHome  nvarchar(5),
@Family_Under18_Live_InHome  nvarchar(5),
@What_Age1  nvarchar(5),
@What_Age2  nvarchar(5),
@What_Age3  nvarchar(5),
@What_Age4  nvarchar(5),
@Smoking_Allowed_InHome  nvarchar(5),
@Currently_Pregnant  nvarchar(3),
@Pregnant_estimated_due_date  nvarchar(20),
@Pregnant_Smoking_Quitdate  nvarchar(20),
@Next_Call_Date  nvarchar(20),
@Next_Call_Time  nvarchar(20),
@Follow_Up_1  nvarchar(50),
@Follow_Up_2  nvarchar(50),
@Follow_Up_3  nvarchar(50),
@Ok_Contact_Again  nvarchar(50),
@On_Leave_Message  nvarchar(50),
@Case_Notes  nvarchar(500),
@Call2_Smoked_Past_7_Days  nvarchar(3),
@Call2_Smoked_Even_Puff_Since_Lasttime  nvarchar(3),
@Call2_Elapsed_Between_Days  nvarchar(4),
@Receiving_Additional_Services nvarchar(3)


as
/*

*/

DECLARE @stsql varchar(2000)
DECLARE @vPrint varchar(1000)
DECLARE @vGroupNum  nvarchar(10)
DECLARE @vCallOrFollowup  varchar(30)
DECLARE @vCallOrFollowupField  varchar(30)
DECLARE @vCallOrFollowupValue  varchar(30)


IF REPLACE(@Pregnant_estimated_due_date,' ','') = ''
begin
      set @Pregnant_estimated_due_date = null
end
IF REPLACE(@Pregnant_Smoking_Quitdate,' ','') = ''
begin
      set @Pregnant_Smoking_Quitdate = null
end
IF REPLACE(@Next_Call_Date,' ','') = ''
begin
      set @Next_Call_Date = null
end
IF REPLACE(@Follow_Up_1,' ','') = ''
begin
      set @Follow_Up_1 = null
end
IF REPLACE(@Follow_Up_2,' ','') = ''
begin
      set @Follow_Up_2 = null
end
IF REPLACE(@Follow_Up_3,' ','') = ''
begin
      set @Follow_Up_3 = null
end


IF @Followup_ID = 0
BEGIN
      INSERT INTO  QuitLine_Call   (Client_ID,  CALL_ID, Counselor_First,
 Counselor_Last, Counselor_Ext, Call_StartTime,
    Call_EndTime, Num_Days_Smoking, Smoking_Per_Day, Started_Smoking_Age, Tried_Quit_Smoking,
    Longest_Abstinence_Smoking, Client_Best_Describes,
    Education, Marital_Status, Family_People_Live_InHome,
    Family_Under18_Live_InHome, What_Age1, What_Age2,
    What_Age3, What_Age4, Smoking_Allowed_InHome,
    Currently_Pregnant, Pregnant_estimated_due_date, Pregnant_Smoking_Quitdate,
    Next_Call_Date, Next_Call_Time, Follow_Up_1, Follow_Up_2, Follow_Up_3,
    Ok_Contact_Again, On_Leave_Message, Case_Notes,
    Call2_Smoked_Past_7_Days,
    Call2_Smoked_Even_Puff_Since_Lasttime,
    Call2_Elapsed_Between_Days, Receiving_Additional_Services)
VALUES(@Client_ID, @Call_ID, @Counselor_First, @Counselor_Last, @Counselor_Ext, @Call_StartTime,
@Call_EndTime, @Num_Days_Smoking, @Smoking_Per_Day, @Started_Smoking_Age, @Tried_Quit_Smoking,
@Longest_Abstinence_Smoking, @Client_Best_Describes, @Education, @Marital_Status, @Family_People_Live_InHome,
@Family_Under18_Live_InHome, @What_Age1, @What_Age2, @What_Age3, @What_Age4, @Smoking_Allowed_InHome,
@Currently_Pregnant, @Pregnant_estimated_due_date, @Pregnant_Smoking_Quitdate, @Next_Call_Date, @Next_Call_Time,
@Follow_Up_1, @Follow_Up_2, @Follow_Up_3, @Ok_Contact_Again, @On_Leave_Message, @Case_Notes,
@Call2_Smoked_Past_7_Days, @Call2_Smoked_Even_Puff_Since_Lasttime, @Call2_Elapsed_Between_Days, @Receiving_Additional_Services)
END
ELSE
BEGIN
      INSERT INTO  QuitLine_Followup   (Client_ID,  Followup_ID, Counselor_First,
 Counselor_Last, Counselor_Ext, Call_StartTime,
    Call_EndTime, Num_Days_Smoking, Smoking_Per_Day, Started_Smoking_Age, Tried_Quit_Smoking,
    Longest_Abstinence_Smoking, Client_Best_Describes,
    Education, Marital_Status, Family_People_Live_InHome,
    Family_Under18_Live_InHome, What_Age1, What_Age2,
    What_Age3, What_Age4, Smoking_Allowed_InHome,
    Currently_Pregnant, Pregnant_estimated_due_date, Pregnant_Smoking_Quitdate,
    Next_Call_Date, Next_Call_Time, Follow_Up_1, Follow_Up_2, Follow_Up_3,
    Ok_Contact_Again, On_Leave_Message, Case_Notes,
    Call2_Smoked_Past_7_Days,
    Call2_Smoked_Even_Puff_Since_Lasttime,
    Call2_Elapsed_Between_Days, Receiving_Additional_Services)
VALUES(@Client_ID, @Followup_ID, @Counselor_First, @Counselor_Last, @Counselor_Ext, @Call_StartTime,
@Call_EndTime, @Num_Days_Smoking, @Smoking_Per_Day, @Started_Smoking_Age, @Tried_Quit_Smoking,
@Longest_Abstinence_Smoking, @Client_Best_Describes, @Education, @Marital_Status, @Family_People_Live_InHome,
@Family_Under18_Live_InHome, @What_Age1, @What_Age2, @What_Age3, @What_Age4, @Smoking_Allowed_InHome,
@Currently_Pregnant, @Pregnant_estimated_due_date, @Pregnant_Smoking_Quitdate, @Next_Call_Date, @Next_Call_Time,
@Follow_Up_1, @Follow_Up_2, @Follow_Up_3, @Ok_Contact_Again, @On_Leave_Message, @Case_Notes,
@Call2_Smoked_Past_7_Days, @Call2_Smoked_Even_Puff_Since_Lasttime, @Call2_Elapsed_Between_Days, @Receiving_Additional_Services)
END



 
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6999698
The IF/ELSE with INSERTs for each should work fine.  And IMHO that's a far better option than EXEC.  If you're getting an error trying this, please post what the error is.

The main thing overall is that, as you've discovered, the table cannot be a variable and no column name can be a variable.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question