动态SQL的问题,求助!
存贮过程中使用的表名和查询条件都要从外部作为参数传入
create proc csearch
@tablename sysname,
@condition varchar(2000)
as
declare @sqlstr varchar(4000)
set @sqlstr = 'declare cur_result cursor for select a,b,c from ' + @tablename + ' where ' + @condition
exec executesql @sqlstr
open cur_result
......
运行时报错:
没有名为cur_result的cursor
有什么办法解决吗?
[解决办法]
create proc csearch
@tablename sysname,
@condition varchar(2000)=NULL
as
DECLARE @WHERE VARCHAR(MAX)
SET @WHERE=''
SELECT @condition=ISNULL(@condition,'')
IF @condition>''
SET @WHERE=' WHERE @condition'
BEGIN
declare @sqlstr nvarchar(4000)
set @sqlstr = 'declare cur_result cursor for select a,b,c from ' + @tablename + '+@WHERE+'
SET @sqlstr=@sqlstr+' open cur_result .....'
EXEC sp_executesql @sqlstr,N'@condition varchar(2000),@condition
end