Solved

INSERT NULL IN SMALLDATETIME DATATYPE

Posted on 2002-05-08
7
811 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
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: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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
C# SQL BULK INSERT CLASS 5 35
SQL Connection (Error 18456) 14 34
Group by and order by clause 28 36
Sql query 34 19
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

757 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

21 Experts available now in Live!

Get 1:1 Help Now