如何将Null参数传递给smalldatetime数据类型 [英] How to Pass Null Parameter into smalldatetime Datatype

问题描述

全部



这里我使用Inser和Update的相同商店程序，当我通过

Hi, All

Here i'am using same store procedure for Inser and Update, when i pass

Dbcon.TerminationDate = DBNull.Value.ToString();

它将显示日期为2014年9月2日上午12:00:00



i希望在TerminationDate列中为null在DB中使用传递空值



谢谢！



我的查询：

it will show date as 02/Sep/2014 12:00:00 AM

i want to be null in TerminationDate column in DB using to pass null value

Thanks !

My Query :

con.Open();
            cmd = new SqlCommand("Insert_StaffMaster_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TerminateDateD", TerminationDate);
           
            return cmd.ExecuteNonQuery();

我的商店处理：

My Store Proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Insert_StaffMaster_SP]
(
@TerminateDateD smalldatetime
)
as
begin
if not exists(select UserIdN from StaffMaster where UserIdN=@UserIdN)
insert into StaffMaster (TerminateDateD) values (@TerminateDateD)
else
update StaffMaster set TerminateDateD=@TerminateDateD where UserIdN=@UserIdN
end

谢谢！

Thanks !

推荐答案

我的理解是你想要将Null值传递给存储过程，所以请使用它。

What i understand is that you want to pass Null value to store procedure so use this.

con.Open();
            cmd = new SqlCommand("Insert_StaffMaster_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TerminateDateD", DBNull.Value);//DBNull.Value
           
            return cmd.ExecuteNonQuery();

如果有任何问题，请告诉我。

If any Question then please let me know.

参数default必须为null。

Parameters default has to be null.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Insert_StaffMaster_SP]
(
@TerminateDateD smalldatetime = NULL
)
as
begin
if not exists(select UserIdN from StaffMaster where UserIdN=@UserIdN)
insert into StaffMaster (TerminateDateD) values (@TerminateDateD)
else
update StaffMaster set TerminateDateD=@TerminateDateD where UserIdN=@UserIdN
end

直接置空而不是终止日期。

如果你想传递null，请再检查一个参数

Put directly null in instead of TerminationDate.
Check with one more parameter if you want to pass null or not

if(ForNullValue==true)
 cmd.Parameters.AddWithValue("@TerminateDateD",null );
else 
 cmd.Parameters.AddWithValue("@TerminateDateD", TerminationDate);

这篇关于如何将Null参数传递给smalldatetime数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！