存储过程WHERE子句错误(应为参数)

Question

ALTER PROCEDURE [dbo].[sp_twa_report1]
(@date1 as DATE,
@date2 as DATE,
@Filter1 as INT,
@Kheadoption as NVARCHAR(10),
@Kcodeoption as NVARCHAR(10),
@Ktypeoption as NVARCHAR(10),
@Shiftoption as INT)
AS
BEGIN
SELECT
wageentry.empno, wageentry.kdate, wageentry.kcode,
empmain.paybookno, 
kamjaricode.kdesc, kamjarihead.khead, kamjarihead.khdesc,
wageentry.shiftno
INTO
#tmpTbl1
FROM
wageentry, empmain, kamjaricode, kamjarihead
WHERE 
wageentry.empno = empmain.empno
AND wageentry.kcode = kamjaricode.kcode
AND kamjaricode.khead = kamjarihead.khead
AND wageentry.kdate >= @date1 AND wageentry.kdate <= @date2
AND (wageentry.shiftno = CASE @Shiftoption 
WHEN 1 THEN 1 
WHEN 2 THEN 1 
WHEN 3 THEN 2 
ELSE 5 
END
OR wageentry.shiftno = CASE @Shiftoption 
WHEN 1 THEN 2 
WHEN 2 THEN 1 
WHEN 3 THEN 2 
ELSE 5 
END
OR wageentry.shiftno = CASE @Shiftoption
WHEN 1 THEN 5  
WHEN 2 THEN 1 
WHEN 3 THEN 2 
ELSE 5 
END)
AND ((@Kheadoption IS NULL) OR (kamjaricode.khead = @Kheadoption))
AND ((@Kcodeoption IS NULL) OR (kamjaricode.kcode = @Kcodeoption))
AND ((@Ktypeoption IS NULL) OR (kamjaricode.ktype = @Ktypeoption))
-- More code here
End

上面的存储过程在Management Studio中运行良好，但当我从Winforms应用程序中运行它时，我会收到一个错误：

过程或函数sp_twa_report1'需要参数'@Kheadoption'，但未提供该参数。

默认情况下，参数@Kheadoption, @Kcodeoption, @Ktypeoption为null。因此，根据我的理解，应该忽略查询的这些行。我哪里错了？

我的Winforms代码是：

commandText = "sp_twa_report1";
SqlCommand command = new SqlCommand(commandText, cs);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@date1", SqlDbType.Date).Value = mySdate;
command.Parameters.Add("@date2", SqlDbType.Date).Value = myEdate;
command.Parameters.Add("@Filter1", SqlDbType.Int).Value = Filter1;
command.Parameters.Add("@Kheadoption",SqlDbType.NVarChar).Value = Kheadoption;
command.Parameters.Add("@Kcodeoption", SqlDbType.NVarChar).Value = Kcodeoption;
command.Parameters.Add("@Ktypeoption", SqlDbType.NVarChar).Value = Ktypeoption;
command.Parameters.Add("@Shiftoption", SqlDbType.Int).Value = myShiftno;

cs.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
cs.Close();

Answer 1

参数@Kheadoption、@Kcodeoption和@Ktypeoption默认为null。因此，根据我的理解，应该忽略查询的这些行。

在传递存储过程的参数时，请考虑使用DBNull而不是null。

根据MSDN：向服务器发送null参数值时，必须指定DBNull，而不是null。