日期:2014-05-18  浏览次数:20400 次

动态的构造查询
表:
create table SBLX

int ID
primary key
identity(1,1),
MC varchar(50) not null


存储过程:
CREATE procedure SBLX_select
(
@intID int = null,
@chvMC varchar(50) = null,
@debug int = 0
)
as
set nocount on declare @intErrorCode int,
@chvQuery varchar(8000),
@chvWhere varchar(8000)
select @intErrorCode = @@error,
@chvQuery = 'set QUOTED_IDENTIFIER OFF select ID,MC from SBLX ',
@chvWhere = ''
if @intErrorCode = 0 and @intID is not null
begin
set @chvWhere = @chvWhere + ' ID = ''' + @intID + ''' or' 
select @intErrorCode = @@error
end
if @intErrorCode = 0 and @chvMC is not null
begin
set @chvWhere = @chvWhere + ' MC = ''' + @chvMC + ''' or'
select @intErrorCode = @@error
end
if @debug <> 0 select @chvWhere chvWhere
if @intErrorCode = 0 and substring(@chvWhere,len(@chvWhere) -2,3) =' or'
begin
set @chvWhere = substring(@chvWhere,1,len(@chvWhere)-3)
select @intErrorCode = @@error
if @debug <> 0 select @chvWhere chvWhere
end
if @intErrorCode = 0 and len(@chvWhere)>0
begin
set @chvQuery = @chvQuery + ' where ' + @chvWhere
select @intErrorCode = @@error
end
if @debug <> 0
select @chvQuery Query
if @intErrorCode = 0
begin
exec(@chvQuery)
select @intErrorCode = @@error
end
return @intErrorCode
GO

当我在查询分析器中输入
SBLX_select @intID = null,@chvMC = '查询条件'
能得到我想要的结果

当我在查询分析器中输入
SBLX_select @intID = 1的时候
返回了这样的一个错误:
将 varchar 值 ' =ID '' 转换为数据类型为 int 的列时发生语法错误。
我怎么才能当我输入SBLX_select 1
这个的时候 返回的是表中ID = 1的记录了??

------解决方案--------------------

SQL code

set @chvWhere = @chvWhere +   ' ID =   ' ' ' +  @intID +    ' ' ' or '  
改为
set @chvWhere = @chvWhere +   ' ID =   ' ' ' +  rtrim(@intID) +    ' ' ' or ' 
需要转换一下:
也可用cast/convert,rtrim必须为可隐性转换为 varchar 的数据类型