Solved

INSERT NULL IN SMALLDATETIME DATATYPE

Posted on 2002-05-08
7
816 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql update 2 38
Trigger C# code inside the SQL Server 6 34
SQL Syntax 6 32
Convert time stamp to date 2 57
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…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…

733 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