我想用存储过程实现批量删除:
存储过程如下:
create proc Delete
@message varchar(50)delete from Users where Id int(@message)@message是从代码中拼接的条件类似于:
@message='a,b,c,d,e'运行程序后发现报异常大概意思是@message是用数字拼接的,但是在这一块却变成了varchar类型试问这个问题该如何解决啊?提前谢了
存储过程如下:
create proc Delete
@message varchar(50)delete from Users where Id int(@message)@message是从代码中拼接的条件类似于:
@message='a,b,c,d,e'运行程序后发现报异常大概意思是@message是用数字拼接的,但是在这一块却变成了varchar类型试问这个问题该如何解决啊?提前谢了
@message varchar(50)
as
delete from Users where CHARINDEX(convert( varchar,id ),@message)>0
@message varchar(50) delete from Users where Id in (@message)
@message varchar(50) delete from Users where Id int(@message) --这是笔误么? 呵呵 也许改过来就对了 如果你的ID是字符型的 如果ID是int型 就看我上面写的
@message varchar(50)
as
declare @sql varchar(8000)
set @sql='delete from Users where Id in('''+replace(@message,',',''',''')+''')'
exec(@sql)
go
@message varchar(200)
As
Set @message=''''+Replace(@message,',',''',''')+''''
Exec ('delete from Users where Id in('+@message+')')
go
@message varchar(50) delete from Users where convert(nvarchar,Id) in (@message)
IF OBJECT_ID('[Delete]') IS NOT NULL
DROP PROCEDURE [Delete]
GO
CREATE PROCEDURE [Delete]
@unionStr nvarchar(4000)
AS
DECLARE @indexLast int
DECLARE @index int
DECLARE @max int
SET @indexLast = 1
SET @index = 0
SET @max = len(@unionStr)DECLARE @delSQL nvarchar(MAX)
WHILE 1 = 1
BEGIN
SET @index = @index + 1
IF @index >= @max
BREAK
SET @indexLast = @index
SET @index = charindex(',',@unionStr,@index)
IF @index = 0 SET @index = @max + 1
SET @delSQL = 'delete from Users where Id = '+ (substring(@unionStr,@indexLast,@index-@indexLast))
PRINT @delSQL
EXEC (@delSQL)
END
GO--创建测试数据表
IF OBJECT_ID('[Users]') IS NOT NULL
DROP TABLE [Users]
GO
CREATE TABLE [Users] (Id int CONSTRAINT PK_ID PRIMARY KEY )
INSERT INTO [Users]
SELECT '813' UNION ALL
SELECT '823' UNION ALL
SELECT '833' UNION ALL
SELECT '843' UNION ALL
SELECT '853' UNION ALL
SELECT '863'
GO---------测试部分---------
SELECT * FROM [Users]
EXEC [Delete] '813,833,843'
SELECT * FROM [Users]/*结果
Id
-----------
813
823
833
843
853
863delete from Users where Id = 813
delete from Users where Id = 833
delete from Users where Id = 843
Id
-----------
823
853
863
*/
DROP TABLE [Users]
GO
CREATE TABLE [Users] (Id int CONSTRAINT PK_ID PRIMARY KEY )
INSERT INTO [Users]
SELECT '813' UNION ALL
SELECT '823' UNION ALL
SELECT '833' UNION ALL
SELECT '843' UNION ALL
SELECT '853' UNION ALL
SELECT '863'
GOcreate proc p_Delete
@message varchar(50)
as
declare @sql varchar(8000)
set @sql='delete from Users where Id in('''+replace(@message,',',''',''')+''')'
exec(@sql)
goexec p_Delete @message='813,812'SELECT * FROM [Users]