如何在存储过程里生成where语句

[李天平]

这样的存储过程如何写

CREATE PROCEDURE sp_Forums_GetTopics
@ForumID int,
@Subject varchar(50),
@MemberName varchar(50),
@AddedDate datetime
AS

DECLARE @strwhere varchar(500)
set @strwhere='(1=1)'

if @ForumID is not null
begin
SET @strwhere=' and '+@strwhere+'ForumID = '+@ForumID
end

if @Subject is not null
begin
SET @strwhere=' and '+@strwhere+'Subject='+@Subject
end

if @MemberName is not null
begin
SET @strwhere=' and '+@strwhere+'MemberName='+@MemberName
end

if @AddedDate is not null
begin
SET @strwhere=' and '+@strwhere+'AddedDate='+@AddedDate
end

SELECT TopicID, Subject, AddedDate, TopicReplies, TopicLastReplyDate, TopicLastPostDate, MemberID, MemberName, Email, ShowEmail
FROM v_Forums_Topics WHERE @strwhere ORDER BY TopicLastPostDate Desc

GO

解析抱错:在关键字 'ORDER' 附近有语法错误。

请问那么如何根据传进来的变量，实现动态得到where语句？

[brightheroes]

SELECT TopicID, Subject, AddedDate, TopicReplies, TopicLastReplyDate, TopicLastPostDate, MemberID, MemberName, Email, ShowEmail
FROM v_Forums_Topics WHERE @strwhere ORDER BY TopicLastPostDate Desc

---->
declare @sql varchar(8000)

set @sql = 'SELECT TopicID, Subject, AddedDate, TopicReplies, TopicLastReplyDate, TopicLastPostDate, MemberID, MemberName, Email, ShowEmail'
+ @strwhere + 'ORDER BY TopicLastPostDate Desc'
exec(@sql)

[CalvinDotNet]

用动态SQL语句
EXEC Sp_ExecuteSql @ExecSql

[gaoxiaospring]

用动态SQL可以.如上.
但用动态SQL必须对动态执行的对象有权限才行;如果不用动态SQL就不一定对表有权限,只要能执行存储过程就可以了.

[xinshaw]

用字串类型，将SQL语句和参数连接起来，最后Exec

Create Procedure dbo.up_Query
@ID int,
@Text nvarchar(50)
begin
declare @Sql nvarchar(500)
set @Sql = 'select * from dbo.Table where ID = ''' + @ID + ''' or Text like ''%' + @Text + '%'''
Exec(@Sql)
end
go