如何 控制传入的参数减少存储过程的代码量

-----新建一个表

create table TA

(Pur_No  varchar(13),

 pur_Name varchar(40),

 remark varchar(8000)

)

 

------------------------

插入数据

insert into  TA

select 'PPA1000000001','Good1','Good1'

union all

select 'PPA1000000002','Good2','Good2'

union all

select 'PPA1000000003','Good3','Good3'

 

--------------------------------

建立一个存储过程

create PROCEDURE [dbo].[SP_Test]
 -- Add the parameters for the stored procedure here
 @Pur_No char(13)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT OFF;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Insert statements for procedure here
 select * from  TA
 WHERE (@Pur_No = '' or @Pur_No is null or Pur_No=@Pur_No)
END

 

----------------------------------------

执行存储过程

create PROCEDURE [dbo].[SP_Test]
 -- Add the parameters for the stored procedure here
 @Pur_No char(13)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT OFF;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Insert statements for procedure here
 select * from  TA
 WHERE (@Pur_No = '' or @Pur_No is null or Pur_No=@Pur_No)
END

-----------------------------------------

测试

exec SP_Test ''
exec SP_Test null
exec SP_Test ' PPA1000000002'

 

----------------------------------------------------

利用上述存储过程的好处

以往我们传入一个参数@Pur_No，一般会在存储过程中写if  (@Pur_No='' or @Pur_No  is null)

else　不为空 做下一步操作

 

-------------------------------------------------------------