另外类似的and连接,给LZ个例子declare @sql varchar(1000) set @sql='select * from T where 1=1 'if @id is not null set @sql=@sql+' and id='+rtirm(@id)if @name is not null set @sql=@sql+' and name like '''+rtrim(@name)+'''%'''exec(@sql)
ALTER PROCEDURE [dbo].[BX_PROC_SELECTARTICLEKEYWORDSTYPEID] @BX_article_stateId INT=0, @BX_article_userId INT=0, @BX_article_areaId INT=0, @BX_article_typeId INT=0, @BX_articleKeyWords VARCHAR(200) --convert(float,字段名) AS BEGIN DECLARE @sql VARCHAR(2000) --select * from news where key like '%key%' and typeID=A.typeID SET @sql = 'select * from BX_Article' IF @BX_article_stateId<>0 OR @BX_article_userId<>0 OR @BX_article_typeId<>0 OR @BX_article_areaId<>0 SET @sql=@sql+' where BX_articleKeyWords like ''%'+@BX_articleKeyWords+'%'''
IF @BX_article_stateId<>0 SET @sql = @sql+' And BX_article_stateId='''+STR(@BX_article_stateId)+''''
IF @BX_article_userId<>0 SET @sql = @sql+' And BX_article_userId='''+STR(@BX_article_userId)+''''
IF @BX_article_typeId<>0 SET @sql = @sql+' And BX_article_typeId='''+STR(@BX_article_typeId)+''''
IF @BX_article_areaId<>0 SET @sql = @sql+' BX_article_areaId='''+STR(@BX_article_areaId)+''''
SET @sql = @sql+' order by BX_articleAttention desc ' EXEC (@sql) END GO
--把STR改为LTRIMALTER PROCEDURE [dbo].[BX_PROC_SELECTARTICLEKEYWORDSTYPEID] @BX_article_stateId INT=0, @BX_article_userId INT=0, @BX_article_areaId INT=0, @BX_article_typeId INT=0, @BX_articleKeyWords VARCHAR(200) --convert(float,字段名) AS BEGIN DECLARE @sql VARCHAR(2000) --select * from news where key like '%key%' and typeID=A.typeID SET @sql = 'select * from BX_Article' IF @BX_article_stateId<>0 OR @BX_article_userId<>0 OR @BX_article_typeId<>0 OR @BX_article_areaId<>0 SET @sql=@sql+' where BX_articleKeyWords like ''%'+@BX_articleKeyWords+'%'''
IF @BX_article_stateId<>0 SET @sql = @sql+' And BX_article_stateId='''+LTRIM(@BX_article_stateId)+''''
IF @BX_article_userId<>0 SET @sql = @sql+' And BX_article_userId='''+LTRIM(@BX_article_userId)+''''
IF @BX_article_typeId<>0 SET @sql = @sql+' And BX_article_typeId='''+LTRIM(@BX_article_typeId)+''''
IF @BX_article_areaId<>0 SET @sql = @sql+' BX_article_areaId='''+LTRIM(@BX_article_areaId)+''''
SET @sql = @sql+' order by BX_articleAttention desc ' EXEC (@sql) END GO
如 Rtrim(@变量)
总是 报 将数据类型 varchar 转换为 float 时出错。
set @sql='select * from T where 1=1 'if @id is not null
set @sql=@sql+' and id='+rtirm(@id)if @name is not null
set @sql=@sql+' and name like '''+rtrim(@name)+'''%'''exec(@sql)
然后将print的结果放到查询分析器一看就知道哪里拼接错误了
@BX_article_stateId INT=0,
@BX_article_userId INT=0,
@BX_article_areaId INT=0,
@BX_article_typeId INT=0,
@BX_articleKeyWords VARCHAR(200)
--convert(float,字段名)
AS
BEGIN
DECLARE @sql VARCHAR(2000)
--select * from news where key like '%key%' and typeID=A.typeID
SET @sql = 'select * from BX_Article'
IF @BX_article_stateId<>0 OR @BX_article_userId<>0 OR @BX_article_typeId<>0 OR @BX_article_areaId<>0
SET @sql=@sql+' where BX_articleKeyWords like ''%'+@BX_articleKeyWords+'%'''
IF @BX_article_stateId<>0
SET @sql = @sql+' And BX_article_stateId='''+STR(@BX_article_stateId)+''''
IF @BX_article_userId<>0
SET @sql = @sql+' And BX_article_userId='''+STR(@BX_article_userId)+''''
IF @BX_article_typeId<>0
SET @sql = @sql+' And BX_article_typeId='''+STR(@BX_article_typeId)+''''
IF @BX_article_areaId<>0
SET @sql = @sql+' BX_article_areaId='''+STR(@BX_article_areaId)+''''
SET @sql = @sql+' order by BX_articleAttention desc '
EXEC (@sql)
END
GO
@BX_article_stateId INT=0,
@BX_article_userId INT=0,
@BX_article_areaId INT=0,
@BX_article_typeId INT=0,
@BX_articleKeyWords VARCHAR(200)
--convert(float,字段名)
AS
BEGIN
DECLARE @sql VARCHAR(2000)
--select * from news where key like '%key%' and typeID=A.typeID
SET @sql = 'select * from BX_Article'
IF @BX_article_stateId<>0 OR @BX_article_userId<>0 OR @BX_article_typeId<>0 OR @BX_article_areaId<>0
SET @sql=@sql+' where BX_articleKeyWords like ''%'+@BX_articleKeyWords+'%'''
IF @BX_article_stateId<>0
SET @sql = @sql+' And BX_article_stateId='''+LTRIM(@BX_article_stateId)+''''
IF @BX_article_userId<>0
SET @sql = @sql+' And BX_article_userId='''+LTRIM(@BX_article_userId)+''''
IF @BX_article_typeId<>0
SET @sql = @sql+' And BX_article_typeId='''+LTRIM(@BX_article_typeId)+''''
IF @BX_article_areaId<>0
SET @sql = @sql+' BX_article_areaId='''+LTRIM(@BX_article_areaId)+''''
SET @sql = @sql+' order by BX_articleAttention desc '
EXEC (@sql)
END
GO