Solved

INSERT NULL IN SMALLDATETIME DATATYPE

Posted on 2002-05-08
7
812 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:
ScottPletcher 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:ScottPletcher
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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:ScottPletcher
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:ScottPletcher
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now