where strName in ( ''''+replace('123,111,001,887',',',''',''')+'''')
declare @where varchar(100) Declare @sqlstr varchar(8000) set @where='123,111,001,887'set @where=''''+replace(@where,',',''',''')+'''' select @where select @sqlstr='select * from tablename where strName in('+ @where+')' exec(@sqlstr)
我需要的是写在存储过程里面,不是在C#里面CREATE PROCEDURE sf_Static_Frequency ( @my_Type char(2) = '', @my_items nvarchar(4000) = '' ) ASIF @my_Type = 'a1' BEGIN update ShowFrequency set Showa1Times = Showa1Times+ 1 where TicketNum in ( ''''+replace(@my_items,',',''',''')+'''') END ELSE IF @my_Type = 'a2' BEGIN update ShowFrequency set Showa2Times = Showa2Times +1 where TicketNum in ( ''''+replace(@my_items,',',''',''')+'''') END ELSE BEGIN RETURN END GO// 执行不了啊
--用个函数吧 create function GetRecords(@str nvarchar(4000)) returns @Rec table (Record nvarchar(200)) as begin declare @s nvarchar(4000) declare @r nvarchar(4000) declare @i nvarchar(4000) set @s=@str set @i=CHARINDEX(',',@s) while @i>0 begin set @r=left(@s,@i-1) insert @Rec values (@R) set @s=right(@s,len(@s)-@i) set @i=CHARINDEX(',',@s) end if len(@s)>0 insert @Rec values (@s) return end goCREATE PROCEDURE sf_Static_Frequency ( @my_Type char(2) = '', @my_items nvarchar(4000) = '' ) ASIF @my_Type = 'a1' BEGIN update ShowFrequency set Showa1Times = Showa1Times+ 1 from ShowFrequency a inner join dbo.GetRecords(@my_items) b on TicketNum=b.Record END ELSE IF @my_Type = 'a2' BEGIN update ShowFrequency set Showa2Times = Showa2Times +1 from ShowFrequency a inner join dbo.GetRecords(@my_items) b on TicketNum=b.Record END ELSE BEGIN RETURN END GO
where strName in ( ''''+replace('123,111,001,887',',',''',''')+'''')
哈哈,不好意思,想难啦 CREATE PROCEDURE sf_Static_Frequency ( @my_Type char(2) = '', @my_items nvarchar(4000) = '' ) AS select @my_items=''''+replace(@where,',',''',''')+'''' IF @my_Type = 'a1' BEGIN exec('update ShowFrequency set Showa1Times = Showa1Times+ 1 where TicketNum in ('+@my_items+')') END ELSE IF @my_Type = 'a2' BEGIN exec('update ShowFrequency set Showa2Times = Showa2Times +1 where TicketNum in ('+@my_items+')') END ELSE BEGIN RETURN END GO
select ''''+replace('123,111,001,887',',',''',''')+''''
Declare @sqlstr varchar(8000)
set @where='123,111,001,887'set @where=''''+replace(@where,',',''',''')+''''
select @where
select @sqlstr='select * from tablename where strName in('+
@where+')'
exec(@sqlstr)
(
@my_Type char(2) = '',
@my_items nvarchar(4000) = ''
)
ASIF @my_Type = 'a1'
BEGIN
update ShowFrequency set Showa1Times = Showa1Times+ 1 where TicketNum in ( ''''+replace(@my_items,',',''',''')+'''') END
ELSE IF @my_Type = 'a2'
BEGIN
update ShowFrequency set Showa2Times = Showa2Times +1 where TicketNum in ( ''''+replace(@my_items,',',''',''')+'''')
END
ELSE
BEGIN
RETURN
END
GO//
执行不了啊
create function GetRecords(@str nvarchar(4000))
returns @Rec table (Record nvarchar(200))
as
begin
declare @s nvarchar(4000)
declare @r nvarchar(4000)
declare @i nvarchar(4000)
set @s=@str
set @i=CHARINDEX(',',@s)
while @i>0
begin
set @r=left(@s,@i-1)
insert @Rec values (@R)
set @s=right(@s,len(@s)-@i)
set @i=CHARINDEX(',',@s)
end
if len(@s)>0
insert @Rec values (@s) return
end
goCREATE PROCEDURE sf_Static_Frequency
(
@my_Type char(2) = '',
@my_items nvarchar(4000) = ''
)
ASIF @my_Type = 'a1'
BEGIN
update ShowFrequency set Showa1Times = Showa1Times+ 1 from ShowFrequency a
inner join dbo.GetRecords(@my_items) b on TicketNum=b.Record
END
ELSE IF @my_Type = 'a2'
BEGIN
update ShowFrequency set Showa2Times = Showa2Times +1 from ShowFrequency a
inner join dbo.GetRecords(@my_items) b on TicketNum=b.Record
END
ELSE
BEGIN
RETURN
END
GO
CREATE PROCEDURE sf_Static_Frequency
(
@my_Type char(2) = '',
@my_items nvarchar(4000) = ''
)
AS
select @my_items=''''+replace(@where,',',''',''')+''''
IF @my_Type = 'a1'
BEGIN
exec('update ShowFrequency set Showa1Times = Showa1Times+ 1 where TicketNum in ('+@my_items+')') END
ELSE IF @my_Type = 'a2'
BEGIN
exec('update ShowFrequency set Showa2Times = Showa2Times +1 where TicketNum in ('+@my_items+')')
END
ELSE
BEGIN
RETURN
END
GO