在存储过程中动态生成 查询语句 的问题

[ssuunnbbiirrdd]

有这样一个很简单的匹配查询:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.Jn_News_SearchEnding
(
@Title Nvarchar(32),
@Name Nvarchar(16),
@AdUserId Int,
@AuditingId Int,
@Result Int OutPut
)
AS
SELECT * FROM News_View WHERE Title=@Title AND Name=@Name AND AdUserId=@AdUserId AND AuditingId=@AuditingId ORDER BY Id DESC

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

现在的问题是: 如果传入的 参数(比如说@title)是 空值,那么,查询语句就去掉 title=@title这个条件,查询语句就变成了这样:

SELECT * FROM News_View WHERE AND Name=@Name AND AdUserId=@AdUserId AND AuditingId=@AuditingId ORDER BY Id DESC
这个存储过程怎么写??

[phantomMan]

把查询语句改为:

SELECT * FROM News_View WHERE (@Title is null or Title=@Title) AND Name=@Name AND AdUserId=@AdUserId AND AuditingId=@AuditingId ORDER BY Id DESC

[ssuunnbbiirrdd]

不是这个意思,title只是我举的一个例子,
我的意思是 这个查询语句 根据传入的参数 是否为空 而动态生成,如果为空,那么就不要这个条件

[paoluo]

可以这么写。

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.Jn_News_SearchEnding
(
@Title Nvarchar(32),
@Name Nvarchar(16),
@AdUserId Int,
@AuditingId Int,
@Result Int OutPut
)
AS
Declare @SWhere Varchar(1000)
Set @SWhere=IsNull(' And Title='''+@Title+'''','')+IsNull(' And Name='''+@Name+'''','')+IsNull(' And AdUserId='+Rtrim(@AdUserId),'')+IsNull(' And AuditingId='+Rtrim(@AuditingId),'')
EXEC('SELECT * FROM News_View WHERE 1=1 '+@SWhere+' ORDER BY Id DESC')
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[ssuunnbbiirrdd]

thx!!已经在存储过程里面执行成功了,先给分 再研究!!!!