要利用存储过程删除一组数据(条件字段为标识列,就是int类型)
我晓得要删除一组数据是利用in来,但是,如果在存储过程来接收数据呢?因为一组数据应该是{0,1,2}这样的类型,肯定要有逗号,而有逗号就应该是varchar类型,但是我的条件确是int类型..
有点晕..希望前辈来解决..谢谢
我晓得要删除一组数据是利用in来,但是,如果在存储过程来接收数据呢?因为一组数据应该是{0,1,2}这样的类型,肯定要有逗号,而有逗号就应该是varchar类型,但是我的条件确是int类型..
有点晕..希望前辈来解决..谢谢
set @idlist='1,2,3'
exec('select * from tbname where fdname in('+@idlist+')')
我郁闷,我的条件字段是int类型...
set @idlist='1,2,3'
print 'select * from tbname where fdname in('+@idlist+')'
--select * from tbname where fdname in(1,2,3)
结果
@BooksId varchar(100)
AS
begin Transaction
declare @errorSum int
set @errorSum = 0
delete from ReaderComments where BookId in('+@BooksId+')
set @errorSum = @errorSum + @@error
delete from OrderBook where BookId in('+@BooksId+')
set @errorSum = @errorSum + @@error
delete from Books where Id in('+@BooksId +')
set @errorSum = @errorSum + @@error
if(@errorSum <> 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go------------------------------我是这样调用..Exec proc_delete_Books_id '2,3'
----------------------最后的结果:服务器: 消息 245,级别 16,状态 1,过程 proc_delete_Books_id,行 7
将 varchar 值 '+@BooksId+' 转换为数据类型为 int 的列时发生语法错误。....谢谢帮忙再看一下啦..
declare @str varchar(100)
set @r='1,2'
set @str='select * from t1 where AutoID in('+@r+')'
exec( @str)这样
create Procedure proc_delete_Books_id
@BooksId varchar(100)
AS
declare @sql varchar(8000)
set @sql='
begin Transaction
declare @errorSum int
set @errorSum = 0
delete from ReaderComments where BookId in('+@BooksId+')
set @errorSum = @errorSum + @@error
delete from OrderBook where BookId in('+@BooksId+')
set @errorSum = @errorSum + @@error
delete from Books where Id in('+@BooksId +')
set @errorSum = @errorSum + @@error
if(@errorSum <> 0)
begin
rollback transaction
end
else
begin
commit transaction
end'
exec(@sql)
go