存储过程中的简单查询

Need to make a stored procedure that will do a simple search on a table, plus have 3 parameters, the first two are a date range, the third is if different from NULL will be included in the WHERE, if equal to NULL then not seek that value, as I can do this search?

需要制作一个存储过程，对表进行简单搜索，加上有3个参数，前两个是日期范围，第三个是如果不同于NULL将包含在WHERE中，如果等于NULL则不寻求这个价值，因为我可以做这个搜索？

8 个解决方案

#1

Where @parameter1 = ColA 
AND @parameter2 = ColB 
and ISNULL(@parameter3, ColC) = ColC

#2

try this..
Create Prucedure test

试试这个..创建Prucedure测试

@p1 int,@p2 int,@p3 nvarchar(50)

@ p1 int，@ p2 int，@ p3 nvarchar（50）

As

Begin

If @p3 is null

如果@ p3为null

Select * from table where field1=@p1 and field2=@p2

从表中选择*，其中field1 = @ p1，field2 = @ p2

Else
Select * from table where field1=@p1 and field2=@p2 and field3=@p3
End

Sorry for improper formatting this is because i am sending the answer from a cell phone

抱歉格式不正确，这是因为我正在通过手机发送答案

#3

WHERE @parameter IS NULL or field = @parameter

#4

Yes! you can do that by passing the three variables to the host program and write a customized CURSOR SELECT using them. Then open the cursor and throw the resultset. Your search results are now ready to use. If needed syntactical help, revert with the host language and specifications you are using. Do post the challenges and solution you got for the same, may help others.

是!您可以通过将三个变量传递给宿主程序并使用它们编写自定义的CURSOR SELECT来实现。然后打开游标并抛出结果集。您的搜索结果现在可以使用了。如果需要语法帮助，请使用您正在使用的主机语言和规范进行恢复。发布您所获得的挑战和解决方案，可以帮助他人。

#5

Try the following. Note that if Column3 could be null then you need to use the isnull() function on the column name as well as you can't compare null values. So if the data type of Column3 is int then you could use: and isnull(Column3, 0) = isnull(@param3, isnull(Column3, 0)).

请尝试以下方法。请注意，如果Column3可以为null，那么您需要在列名上使用isnull（）函数，以及您无法比较空值。因此，如果Column3的数据类型是int，那么您可以使用：和isnull（Column3,0）= isnull（@ param3，isnull（Column3,0））。

create procedure Test
    @param1 varchar(50),
    @param2 varchar(50),
    @param3 varchar(50) = null
as
begin
    select *
    from Table1
    where Column1 = @param1
    and Column2 = @param2
    and Column3 = isnull(@param3, Column3)
end

#6

The general form of what you are attempting to perform is a dynamic query. See the solution in this problem update statement not executing

您尝试执行的操作的一般形式是动态查询。请参阅此问题更新语句中的解决方案未执行

#7

I am using the adventure works database.

我正在使用冒险作品数据库。

 CREATE PROCEDURE upContactDetails
        @FirstName  [nvarchar](50),
        @LastName   [nvarchar](50),
        @MiddleName [nvarchar](50)
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT     Title
             , FirstName
             , MiddleName
             , LastName
             , EmailAddress
    FROM       Person.Contact
    WHERE (@MiddleName IS NULL or MiddleName = @MiddleName) 
          AND FirstName = @FirstName
          AND LastName  = @LastName 
    END
    GO

#8

Imagine that You're having a transaction table where you store transaction Number And Transaction Date as shown Below.

想象一下，您有一个交易表，您可以在其中存储交易编号和交易日期，如下所示。

CREATE TABLE [dbo].[TrnMast](
    [TrnNo] [numeric](10, 0) NOT NULL,
    [AcYear] [int] NOT NULL,
    [Comp_Code] [varchar](5) NOT NULL,
    [InvNo] [varchar](20) NULL,
    [TrnDate] [datetime] NULL,
    [P_Code] [varchar](5) NULL,
    [Amount] [money] NULL,
    [Remark] [varchar](50) NULL
 CONSTRAINT [PK_TrnMast_1] PRIMARY KEY CLUSTERED 
(
    [TrnNo] ASC,
    [Comp_Code] ASC,
    [AcYear] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now Look at stored procedure with 3 parameters having two date range paramaters and TrnNo Below.

现在查看具有3个参数的存储过程，这些参数具有两个日期范围参数和TrnNo Below。

CREATE PROCEDURE [dbo].[SP_TrnMast] 
    @FTrnDate SmallDateTime = Null,
    @TTrnDate SmallDateTime = Null,
    @AcYear Int
AS
Begin   
        Select * From TrnMast           
        Where (@FTrnDate Is Null Or TrnMast.TrnDate >= @FTrnDate) 
          And (@TTrnDate Is Null Or TrnMast.TrnDate <= @TTrnDate) 
          And TrnMast.AcYear = @AcYear 

End

#1

Where @parameter1 = ColA 
AND @parameter2 = ColB 
and ISNULL(@parameter3, ColC) = ColC

#2