USE [ZSWK]
GO
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+’‘’‘+’,’
