replace问题
create database GuideSale
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[tbProdWage_Cost] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[tbProdWage_Cost]
GO
CREATE TABLE [dbo].[tbProdWage_Cost] (
[Cust_No] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[Seq_No] [int] NULL ,
[Gold_No] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[Kind_No] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[Wage] [numeric](14, 4) NULL ,
[Remark] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[Wage1] [numeric](14, 4) NULL ,
[Wage2] [numeric](14, 4) NULL ,
[barcode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[style_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sale_wage] [numeric](14, 4) NULL ,
[gold_prc] [numeric](14, 4) NULL ,
[prewgt] [numeric](14, 4) NULL ,
[size] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[goodsmode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tbprodwage_cost(barcode)values( '00000001 ')
insert into tbprodwage_cost(barcode)values( '00000002 ')
insert into tbprodwage_cost(barcode)values( '00000003 ')
insert into tbprodwage_cost(barcode)values( '00000004 ')
go
create proc sp_ymh
(
@barcodevarchar(5000)
)
as
declare @aa varchar(5000)
set @aa=replace( ' ' ' ' + @barcode + ' ' ' ', ', ', ' ' ', ' ' ')
select barcode from tbprodwage_cost
where barcode in (@aa)
--问题：
--我执行存储过程时，却没有输出值????
--exec sp_ymh '00000001,00000002,00000003 '
[解决办法]
create proc sp_ymh
(
@barcodevarchar(5000)
)
as
declare @aa varchar(5000)
set @aa=replace( ' ' ' ' + @barcode + ' ' ' ', ', ', ' ' ', ' ' ')
exec ( '
select barcode from tbprodwage_cost
where barcode in ( '+@aa+ ')
')
[解决办法]
create proc sp_ymh (@barcode varchar(5000))
as
select barcode from tbprodwage_cost
where charindex( ', ' + barcode + ', ', ', ' + @aa + ', ') > 0
GO
