INSERT NULL IN SMALLDATETIME DATATYPE

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

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.

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)

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,
...
   

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



 

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.