top后面不能为变量
declare @wheresql nvarchar(1000),@sql nvarchar(1000)
if len(@appname) > 0
select @wheresql = 'appname=' + @appname
if len(@content) > 0
begin
if len(@wheresql) > 0
select @wheresql = @wheresql + 'and content=' + @content
end
....
select @sql = 'select top ' + @topcount + 'from table ' + @wheresql
EXEC sp_executesql @Sql
注意,如果条件变量为数字则以上是对了,如果为字符则必须这样写'and content=''' + @content + ''''
declare @wheresql nvarchar(1000),@sql nvarchar(1000)
if len(@appname) > 0
select @wheresql = 'appname=' + @appname
if len(@content) > 0
begin
if len(@wheresql) > 0
select @wheresql = @wheresql + 'and content=' + @content
end
....
select @sql = 'select top ' + @topcount + 'from table ' + @wheresql
EXEC sp_executesql @Sql
注意,如果条件变量为数字则以上是对了,如果为字符则必须这样写'and content=''' + @content + ''''
select @sql = 'select top ' + @topcount + 'from table ' + @wheresql
EXEC (@Sql)
select @sql
=case isnull(@appname,'')='' then ''
else ' and appname=@appname' end
+case isnull(@content,'')='' then ''
else ' and contentname=@content' end
+case isnull(@sourcename,'')='' then ''
else ' and sourcename=@sourcename' end
,@sql='select top '+cast(@topcount as varchar)+' from [table]'
+case @sql when '' then ''
else ' where '+substring(@sql,5,4000) end
exec sp_executesql @sql
,N'@appname varchar(100),
@content varchar(100),
@sourcename varchar(100)'
,@appname,@content,@sourcename
用ResultSet接受取相应值就可以了