WHERE 1=1
AND EventName=ISNULL(@EventName, EventName)
AND ErrType=ISNULL(@ErrType, ErrType)
AND VisitID=ISNULL(@VisitID, VisitID)
AND EventDate=ISNULL(@VisitID, VisitID)
create PROC p_test
@para1 varchar(100),
@para2 int,
@para3 int,
@para4 datetime
AS
SET NOCOUNT ON
SELECT * FROM t WHERE 1=1 AND EventName=isnull(@para1,EventName) and
ErrType=isnull(@para1,ErrType) and AND VisitID =isnull(@para3,VisitID)
AND EventDate=isnull(@Para4,EventDate)
可以用一句话,在性能上有一些损失:
create PROC p_test
@para1 varchar(100),
@para2 int,
@para3 int,
@para4 datetime
AS
SELECT * FROM FailedEvent WHERE
EventName LIKE @para1+'%' AND
CONVERT(VARCHAR(16),ErrType) LIKE RTRIM(CONVERT(VARCHAR(16),@para2))+'%' AND
CONVERT(VARCHAR(16),VisitID) LIKE RTRIM(CONVERT(VARCHAR(16),@para2))+'%' AND
CONVERT(VARCHAR(16),EventDate,120) LIKE CONVERT(VARCHAR(16),@para4,120)+'%'
是这个意思吗?
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 varchar(10)=null,
@para3 varchar(10)=null,
@para4 varchar(10)=null
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM FailedEvent WHERE 1=1'
IF @para1 IS NOT NULL
SET @sql=@sql+' AND EventName='''+@para1+''''
IF @para2 IS NOT NULL
SET @sql=@sql+' AND ErrType ='''+@para2+''''
IF @para3 IS NOT NULL
SET @sql=@sql+' AND VisitID ='''+@para3+''''
IF @para4 IS NOT NULL
SET @sql=@sql+' AND EventDate='''+@para4+''''
EXEC(@sql)
create PROC p_test
@para1 varchar(100),
@para2 int,
@para3 int,
@para4 datetime
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM t WHERE 1=1'
IF isnull(@para1,'')<>''
SET @sql=@sql+' AND EventName like ''%'+@para1+'%'''
IF isnull(@para2,'')<>''
SET @sql=@sql+' AND ErrType ='+@para2
IF isnull(@para3,'')<>''
SET @sql=@sql+' AND VisitID ='+@para3
IF isnull(@para4,'')<>''
SET @sql=@sql+' AND EventDate='''+@para4+''''
print @sql
EXEC(@sql)
GO