Stored Procedure

david_cheong

我想请问一下有关 SQL Server Stored Procedure...

如果我想做一个 search 的 function，在我的 VB。NET window application 里 search staff record,他们可以 either 用 ID，Name, ic 来search...

可是我的store procedure我是放在 SQL Server 里。然后我pass in 的 parameter 可以是ID and Name,是ID,是Name,是IC,是 ALL, 那我的stored procedure 应该如何check 和query neh???

有人说 6 个 if 来 check... that mean there are 6 same query with different condition will be in the stored procedure...????

thunderstorm

方式一

create procedure sp_Method1
(
     @ID    int,
     @Name  varchar(50),
     @ICNo  varchar(15)
)
as
Begin
    select * from tbCustomer
           where (CustID = @ID or @ID = -1)
                 and (CustName = @Name or @Name = '')
                 and (CustICNo = @ICNo or @ICNo = '')

    return 1
End
go




方式二

create procedure sp_Method2
(
     @ID    int,
     @Name  varchar(50),
     @ICNo  varchar(15)
)
as
Begin

    if (@ID >= 0 and @Name = '' and @ICNo = '')
        select * from tbCustomer where CustID = @ID

    else if (@ID = -1 and @Name <> '' and @ICNo = '')
        select * from tbCustomer where CustName = @Name

    else if (@ID = -1 and @Name = '' and @ICNo <> '')
        select * from tbCustomer where CustICNo = @ICNo

    else if (@ID >= 0 and @Name <> '' and @ICNo = '')
        select * from tbCustomer where CustID = @ID and CustName = @Name

    else if (@ID >= 0 and @Name = '' and @ICNo <> '')
        select * from tbCustomer where CustID = @ID and CustICNo = @ICNo

    else if (@ID = -1 and @Name <> '' and @ICNo <> '')
        select * from tbCustomer where CustName = @Name and CustICNo = @ICNo

    else if (@ID >= 0 and @Name <> '' and @ICNo <> '')
        select * from tbCustomer where CustID = @ID and CustName = @Name and CustICNo = @ICNo

    return 1
End
go

[ 本帖最后由 thunderstorm 于 28-9-2005 01:36 PM 编辑 ]