存储过程中如何使用like?

[fuzhan820]

存储过程中使用like
如WHERE OUTID like '%'+ PRMOUTID+'%' AND NAME LIKE '%'+PRMNAME+'%';
以 上出错的?

[uno]

SQL语句没有分号

[cpp2017]

可以用分号

[cpp2017]

PRMOUTID 和 PRMNAME是什么? 类型对不对?

[kexian]

WHERE OUTID like '%PRMOUTID%' AND NAME LIKE '%PRMNAME%'

还有Like只用于字符类型字段.

[chieftech]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC p_bid_file_stockor_select
@bfs_StockerId decimal(9) = -1,
@bfs_CName varchar(100) = '',
@bfs_EName varchar(100) = '',
@bfs_LevelGroup int = -1,
@bfs_Level1 int = -1,
@bfs_Level2 int = -1,
@bfs_Level3 int = -1,
@bfs_Level4 int = -1,
@bfs_Level5 int = -1,
@bfs_UsingMark char(1) = ''
AS
DECLARE @strsql varchar(500)
SET @strsql = 'SELECT * FROM t_bid_file_stockor WHERE NOT bfs_StockerId IS NULL '
IF
@bfs_StockerId = -1 AND
@bfs_CName = '' AND
@bfs_EName = '' AND
@bfs_LevelGroup = -1 AND
@bfs_Level1 = -1 AND
@bfs_Level2 = -1 AND
@bfs_Level3 = -1 AND
@bfs_Level4 = -1 AND
@bfs_Level5 = -1 AND
@bfs_UsingMark = ''
BEGIN
RAISERROR('娌℃湁寰楀埌鏌ヨ鏉′欢',16,1)
END
IF @bfs_StockerId <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_StockerId = ' + @bfs_StockerId
END
IF @bfs_CName <> ''
BEGIN
SET @strsql = @strsql + ' AND bfs_CName LIKE ''%' + @bfs_CName + '%'''
END
IF @bfs_EName <> ''
BEGIN
SET @strsql = @strsql + ' AND bfs_EName LIKE ''%' + @bfs_EName + '%'''
END
IF @bfs_LevelGroup <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_LevelGroup = ' + @bfs_LevelGroup
END
IF @bfs_Level1 <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_Level1 = ' + @bfs_Level1
END
IF @bfs_Level2 <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_Level2 = ' + @bfs_Level2
END
IF @bfs_Level3 <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_Level3 = ' + @bfs_Level3
END
IF @bfs_Level4 <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_Level4 = ' + @bfs_Level4
END
IF @bfs_Level5 <> -1
BEGIN
SET @strsql = @strsql + ' AND bfs_Level5 = ' + @bfs_Level5
END
IF @bfs_UsingMark <> ''
BEGIN
SET @strsql = @strsql + ' AND bfs_UsingMark = ''' + @bfs_UsingMark + ''''
END
EXECUTE (@strsql)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

参考

[fuzhan820]

WHERE OUTID like '%' ¦ ¦to_char(PRMOUTID) ¦ ¦'%' AND NAME LIKE '%' ¦ ¦to_char(PRMNAME) ¦ ¦'%';