sqlserver存储过程实现数据批量删除--分割字符串实现

USE [ZSWK]

GO

/****** Object: StoredProcedure [dbo].[sp_query_掌上微课_用户 *** 作] Script Date: 12/05/2018 11:10:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

– exec sp_query_掌上微课_用户 *** 作 ‘insert’,‘’,‘20181204测试’,‘123456’,‘’,‘测试001’,‘男’,‘’,‘’,‘1’,‘’,‘’,‘123’,‘’

– exec sp_query_掌上微课_用户 *** 作 ‘update’,‘10’,‘test001’,‘’,‘’,‘’,‘’,‘’,‘null’,‘’,‘’,‘’,‘’,‘’

– exec sp_query_掌上微课_用户 *** 作 ‘delete’,‘11’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’,‘null’

ALTER PROC [dbo].[sp_query_掌上微课_用户 *** 作]

@operation varchar(255),

@userId varchar(255),

@userName varchar(255),

@password varchar(255),

@phone varchar(255),

@fullname varchar(255),

@sex varchar(255),

@area varchar(255),

@createAt varchar(255),

@isTeacher varchar(255),

@teacherInfo varchar(4000),

@icon varchar(4000),

@empNumber varchar(255),

@userAccount varchar(255)

AS

BEGIN

SET NOCOUNT ON

IF (@operation = ‘insert’)

BEGIN

INSERT INTO TB_user(user_name,password,fullname,phone,sex,area,create_at,isTeacher,teacher_info,icon,empNumber,userAccount)

VALUES (@userName,@password, @fullname, @phone,@sex,@area,@createAt,@isTeacher,@teacherInfo,@icon,@empNumber,@userAccount)

END

IF (@operation = ‘delete’)

BEGIN

DECLARE @temp nvarchar(200)

DECLARE @splitchar nvarchar(1)

SET @splitchar=‘,’

SET @userId=@userId+@splitchar

WHILE CHARINDEX(@splitchar,@userId)>0

BEGIN

SET @temp=SUBSTRING(@userId,1,CHARINDEX(@splitchar,@userId)-1)

IF(LEN(@temp)>0)

BEGIN

PRINT(@temp)

DELETE from TB_user WHERE user_id =@temp;

END

SET @userId=RIGHT(@userId,LEN(@userId)-CHARINDEX(@splitchar,@userId))

PRINT(@userId)

END

END

IF (@operation = ‘update’)

BEGIN

DECLARE @sql VARCHAR(5000)

DECLARE @whereSql VARCHAR(5000)

SET @sql = ‘UPDATE TB_user SET’

SET @whereSql = ‘’

IF ( @userName !=‘NULL’ and @userName != ‘’)

BEGIN

SET @whereSql =@whereSql+ ’ user_name = ‘+’‘’‘+@userName+’‘’‘+’,’

END

IF ( @password !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ 《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】 ’ password = ‘+’‘’‘+@password+’‘’‘+’,’

END

IF ( @phone !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ phone = ‘+’‘’‘+@phone+’‘’‘+’,’

END

IF ( @fullname !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ fullname = ‘+’‘’‘+@fullname+’‘’‘+’,’

END

IF ( @sex !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ sex = ‘+’‘’‘+@sex+’‘’‘+’,’

END

IF ( @area !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ area = ‘+’‘’‘+@area+’‘’‘+’,’

END

IF ( @createAt !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ create_at = ‘+’‘’‘+@createAt+’‘’‘+’,’

END

IF ( @isTeacher !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ isTeacher = ‘+’‘’‘+@isTeacher+’‘’‘+’,’

END

IF ( @teacherInfo !=‘NULL’)

BEGIN

SET @whereSql =@whereSql+ ’ teacher_info = ‘+’‘’‘+@teacherInfo+’‘’‘+’,’