2.7w+
社区成员
- 全部
在存储过程中动态生成 查询语句 的问题
有这样一个很简单的匹配查询:
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
这个存储过程怎么写??
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
这个存储过程怎么写??
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
ssuunnbbiirrdd 2005-07-27
thx!!已经在存储过程里面执行成功了,先给分 再研究!!!!
paoluo 2005-07-27
可以这么写。
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
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 2005-07-27
不是这个意思,title只是我举的一个例子,
我的意思是 这个查询语句 根据传入的参数 是否为空 而动态生成,如果为空,那么就不要这个条件
我的意思是 这个查询语句 根据传入的参数 是否为空 而动态生成,如果为空,那么就不要这个条件
phantomMan 2005-07-27
把查询语句改为:
SELECT * FROM News_View WHERE (@Title is null or Title=@Title) AND Name=@Name AND AdUserId=@AdUserId AND AuditingId=@AuditingId ORDER BY Id DESC
SELECT * FROM News_View WHERE (@Title is null or Title=@Title) AND Name=@Name AND AdUserId=@AdUserId AND AuditingId=@AuditingId ORDER BY Id DESC
