来自C＃的EXEC sp，参数可能为NULL [英] EXEC sp from C# with parameter that may be NULL

问题描述

我有以下sp：

 创建  PROCEDURE  SheriffSale.usp_combinedsearch 
  @ SaleId   nvarchar （ 25 ）=  NULL ，
  @城市  nvarchar （ 50 ）=  NULL ，
  @ ZipCode   nvarchar （ 10 ）=  NULL ，
  @County   nvarchar （ 25 ）=  NULL ，
  @ JudgementAmountMin   money  =  NULL ，
  @ JudgementAmountMax   money  =  NULL ，
  @AssessedValueMin   money  = < span class =code-keyword> NULL ，
  @AssessedValueMax   money  =  NULL ，
  @ saledatestart  日期 =  NULL ，
  @ saledateend  日期 =  NULL  
  AS  
  SELECT  s.SaleId，s.CaseNumber，s.County，s.Mapsco，s.Plaintiff，s.Defendant，
 s.Address，s.City，s.State，s。 ZipCode，s.JudgementAmount，s.YearOfConstruction，
 s.LegalDesc ription，s .AssessedValue 
  FROM  Sale s 
  WHERE （s.City =  @ City  或  @ City   IS   NULL ）
  AND （s。 ZipCode =  @ ZipCode   OR   @ ZipCode  < span class =code-keyword> IS   NULL ）
  AND （ s.County =  @County   OR   @County   IS   NULL ）
  AND （s.JudgementAmount> =  @ JudgementAmountMin   OR   @ JudgementAmountMin   IS   NULL ）
  AND （s.JudgementAmount< =  @ JudgementAmountMax   OR   @ JudgementAmountMax   IS   NULL ）
  AND （s .AssessedValue> =  @AssessedValueMin   OR   @ JudgementAmountMin   IS   NULL ）
  AND （s.AssessedValue< =  @ JudgementAmountMax   OR   @ JudgementAmountMax   IS   NULL ）
  AND （s.SaleDate> =  @saledatestart  或  @ saledatestart   IS   NULL ）
  AND （s.SaleDate< =  @ saledateend  或  @ saledateend   IS   NULL ）
  ORDER   BY  s.SaleId 
  OPTION （RECOMPILE）

使用C＃我想使用从文本框的用户输入获取的参数来执行sp，这些参数可能包含也可能不包含值。例如，我可能只有city和JudgementAmountMix和JudgementAmountMax的值。我想让查询值来自文本框中的搜索变量。



我还需要将结果返回给调用方法，以便在WPF数据网格中使用。我花了好几天在网上尝试不同的例子而没有结果，我正在进入交付的截止日期。我知道这对于使用SQL Server的任何人来说都是一个非常标准的要求，我只是已经没时间了，并且没有耐心试图找到解决方案。任何帮助将不胜感激，请提供尽可能完整的解决方案，因为我是相当新的。

解决方案

我个人会保持这种情况简单，所以我会为所有参数定义一个值。



对于每个参数，我选择用户输入（如果存在或不存在），然后传递System.DBNull.Value [ ^ ]到程序。



因此设置参数可能是这样的（如果saleId是传递给调用该过程的方法的参数。

 ... 
 command.Parameters.AddWithValue（  @ SaleId， saleId ==  null ？System.DBNull.Value：（ object ）saleId）; 
。 .. 

I have the following sp:

CREATE PROCEDURE SheriffSale.usp_combinedsearch
@SaleId nvarchar(25)   		= NULL,
@City nvarchar(50)    		= NULL,
@ZipCode nvarchar(10)  		= NULL,
@County nvarchar(25)   		= NULL,
@JudgementAmountMin money 	= NULL,
@JudgementAmountMax money 	= NULL,
@AssessedValueMin money   	= NULL,
@AssessedValueMax money   	= NULL,
@saledatestart Date 		= NULL,
@saledateend Date 			= NULL
AS
SELECT s.SaleId, s.CaseNumber, s.County, s.Mapsco, s.Plaintiff, s.Defendant, 
s.Address, s.City, s.State, s.ZipCode, s.JudgementAmount, s.YearOfConstruction,
s.LegalDescription, s.AssessedValue
FROM Sale s
WHERE (s.City = @City OR @City IS NULL)
AND (s.ZipCode = @ZipCode OR @ZipCode IS NULL)
AND (s.County = @County OR @County IS NULL)
AND (s.JudgementAmount >= @JudgementAmountMin OR @JudgementAmountMin IS NULL)
AND (s.JudgementAmount <= @JudgementAmountMax OR @JudgementAmountMax IS NULL)
AND (s.AssessedValue >= @AssessedValueMin OR @JudgementAmountMin IS NULL)
AND (s.AssessedValue <= @JudgementAmountMax OR @JudgementAmountMax IS NULL) 
AND (s.SaleDate >= @saledatestart OR @saledatestart IS NULL)
AND (s.SaleDate <= @saledateend OR @saledateend IS NULL)
ORDER BY s.SaleId
OPTION (RECOMPILE)

Using C# I would like to cause the sp to execute with parameters taken from user input from textboxes which may or may not contain a value. For example, I may have a value for city and for JudgementAmountMix and JudgementAmountMax only. I would like to have the query value the search variables from the textboxes.

I also need to have the result returned to the calling method for use in a WPF datagrid. I have spent days trying different examples on the web with no results and I am running into a deadline for delivery. I know that this has to be a pretty standard type of requirement for anyone working with SQL Server, I just have run out of time and run out of patience trying to find a solution. Any assistance would be appreciated and please provide as complete a solution as possible as I am fairly new at this.

解决方案

Personally I would keep the situation simple so that I would define a value for all parameters.

For each parameter I'd choose either the user input if it exists or if not, then pass System.DBNull.Value[^] to the procedure.

So setting the parameters could be something like (if saleId is a parameter passed to the method calling the procedure.

...
command.Parameters.AddWithValue("@SaleId", saleId == null ? System.DBNull.Value : (object)saleId);
...

这篇关于来自C＃的EXEC sp，参数可能为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！