存储过程批量更新问题 急～

[evaporate]

存储过程
-----------------------------------------------------------
alter PROCEDURE [dbo].[sp_Book_UpdateChapterAllEditorDo]
@BookID int,
@VolumeIDList nvarchar(2000),
@ChapterIDList nvarchar(2000),
@EditorDo char(1)
AS

SET NOCOUNT ON

UPDATE [dbo].[Book_Chapter] SET
EditorDo = @EditorDo
WHERE
[BookID] = @BookID and VolumeID in (@VolumeIDList) and ChapterID in (@ChapterIDList)
--------------------------------------------------
exec [sp_Book_UpdateChapterAllEditorDo] 701242,'1691,1690','5905,5906,5907,5908','6'

执行后提示在将nvarchar 值'5905,5906,5907,5908' 转换成数据类型int 时失败

ChapterID 这个字段是一个整型 但是用参数传递进来是一个拼接的字符串是不是不能这么写 还是有其他的方法？

[肥胖的柠檬]

有','号,先将,号去了

replace('346534,34534,346',',','')

[evaporate]

去掉以后还怎么批量更新呢？
还是只能写成SQL语句？存储过程的语法不能这么写 in (@VolumeIDList) 在传一个'5905,5906,5907,5908' 请教了～

[suifix]

把需要执行的update语句写在字符串里面，然后exec(@Str)

[evaporate]

alter PROCEDURE [dbo].[sp_Book_UpdateChapterAllEditorDo]
@BookID int,
@VolumeIDList nvarchar(2000),
@ChapterIDList nvarchar(2000),
@EditorDo char(1)
AS
declare @SQL nvarchar(2000)
SET NOCOUNT ON

set @SQL = 'UPDATE [dbo].[Book_Chapter] SET
EditorDo = '+@EditorDo+'
WHERE
[BookID] = '+@BookID+' and VolumeID in ('+@VolumeIDList+') and ChapterID in ('+@ChapterIDList+')'

exec @SQL

-----------------------------------------------------------------------------
提示：
消息 245，级别 16，状态 1，过程 sp_Book_UpdateChapterAllEditorDo，第 15 行
在将 varchar 值 'UPDATE [dbo].[Book_Chapter] SET
EditorDo = 6
WHERE
[BookID] = ' 转换成数据类型 int 时失败。

[evaporate]

在线等啊～～～

[ShineLC]

alter PROCEDURE [dbo].[sp_Book_UpdateChapterAllEditorDo]
@BookID int,
@VolumeIDList nvarchar(2000),
@ChapterIDList nvarchar(2000),
@EditorDo char(1)
AS
declare @SQL nvarchar(2000)
SET NOCOUNT ON

set @SQL = 'UPDATE [dbo].[Book_Chapter] SET
EditorDo = '''+@EditorDo+'''
WHERE
[BookID] = '+cast(@BookID as varchar)+' and VolumeID in ('+@VolumeIDList+') and ChapterID in ('+@ChapterIDList+')'

print @SQL
exec @SQL

[evaporate]

还是没解决 提示：
UPDATE [dbo].[Book_Chapter] SET
EditorDo = '6'
WHERE
[BookID] = 701242 and VolumeID in (1691,1690) and ChapterID in (5905,5906,5907,5908)
消息 203，级别 16，状态 2，过程 sp_Book_UpdateChapterAllEditorDo，第 21 行
名称 'UPDATE [dbo].[Book_Chapter] SET
EditorDo = '6'
WHERE
[BookID] = 701242 and VolumeID in (1691,1690) and ChapterID in (5905,5906,5907,5908)' 不是有效的标识符。

[ShineLC]

exec @SQL 改为 exec (@SQL)

[ShineLC]

alter PROCEDURE [dbo].[sp_Book_UpdateChapterAllEditorDo]
@BookID int,
@VolumeIDList nvarchar(2000),
@ChapterIDList nvarchar(2000),
@EditorDo char(1)
AS
declare @SQL nvarchar(2000)
SET NOCOUNT ON

set @SQL = 'UPDATE [dbo].[Book_Chapter] SET
EditorDo = '''+@EditorDo+'''
WHERE
[BookID] = '+cast(@BookID as varchar)+' and VolumeID in ('+@VolumeIDList+') and ChapterID in ('+@ChapterIDList+')'

exec (@SQL)