存储过程通过参数查询指定类别数据或全部数据
数据表生成语句如下：
==============================================
if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N '[dbo].[MemberBaseInfo] ')   and   OBJECTPROPERTY(id,   N 'IsUserTable ')   =   1)
drop   table   [dbo].[MemberBaseInfo]
GO

CREATE   TABLE   [dbo].[MemberBaseInfo]   (
[Name]   [varchar]   (10)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   ,
[Sex]   [int]   NULL  
)   ON   [PRIMARY]
GO
==============================================
“Sex”为性别，1为男，2为女
有两条测试数据：
==============================================
Name                         Sex
--------------------------------------------
lisi                         2
zhangsan                 1
==============================================
我要写一个存储过程，根据输入参数查询“Sex”为相应值的记录，但如果输入值为0时，查询所有记录。这个逻辑只能用一条SQL语句实现，不能用IF……ELSE……等结构。
我写的存储过程如下：
==============================================
DROP   PROCEDURE   P_GetMemberBySexType
GO

CREATE   PROCEDURE   P_GetMemberBySexType
(
@Sex INT
)
AS

SELECT   Name,   Sex
FROM   MemberBaseInfo
WHERE   Sex   =   @Sex

GO
==============================================


------解决方案--------------------
CREATE PROCEDURE P_GetMemberBySexType
(
@Sex INT
)
AS

SELECT Name, Sex
FROM MemberBaseInfo
WHERE isnull(Sex,0)=0 or sex=@sex
go

------解决方案--------------------
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[MemberBaseInfo] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[MemberBaseInfo]
GO

CREATE TABLE [dbo].[MemberBaseInfo] (
[Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sex] [int] NULL
) ON [PRIMARY]
GO

insert MemberBaseInfo select 'lisi ', 2
union all select 'zhangsan ', 1
go

CREATE PROCEDURE P_GetMemberBySexType
(
@Sex INT
)
AS

SELECT Name, Sex
FROM MemberBaseInfo
WHERE Sex = (case when @Sex=0 then Sex else @Sex end)

GO

exec P_GetMemberBySexType 1

exec P_GetMemberBySexType 2

exec P_GetMemberBySexType 0