[b]1、优化前[/b]
[b]2、优化后[/b]
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_demo]
@type varchar(10),
@stuId varchar(10),
@stuNo VARCHAR(20),
@tFlag varchar(1),
@stuClassIds varchar(max)
AS
BEGIN
SET NOCOUNT ON;
-- 定义游标,编辑sql, 取得字段对应关系
DECLARE @TempStuSql VARCHAR(2000)
DECLARE @StuSql VARCHAR(2000)
--创建游标
DECLARE stuCursor CURSOR FOR
SELECT TempStuField,StuField,StuType,isSchoolItem FROM UpgradeStuFieldDefine
WHERE xType = @type
AND xId = @stuId
AND LOWER(TempStuField) != 'id'
AND LOWER(StuField) != 'code'
ORDER BY ID DESC
--开启游标
OPEN stuCursor
DECLARE @TempStuField VARCHAR(5000)
DECLARE @StuField VARCHAR(100)
DECLARE @StuType VARCHAR(1)
DECLARE @isSchoolItem VARCHAR(1)
SET @TempStuSql = ''
SET @StuSql = ''
--fetch row by row
FETCH NEXT FROM stuCursor INTO @TempStuField,@StuField,@StuType,@isSchoolItem --eg: f2 gender 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF @isSchoolItem='0'
SET @TempStuSql = '(select o.id from OptionItem o where o.itemName=tc.' +@TempStuField + ') as ' +@TempStuField + ',' + @TempStuSql
ELSE
SET @TempStuSql = 'tc.' +@TempStuField + ',' + @TempStuSql
SET @StuSql = @StuField + ',' + @StuSql
FETCH NEXT FROM stuCursor INTO @TempStuField,@StuField,@StuType,@isSchoolItem
END
--@TempStuField @StuField @StuType 每个变量每次只能保存一行数据对应的一条数据
--print @TempStuSql
--print @StuSql
--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,
--sname,gender,mobile,address,email,
CLOSE stuCursor
DEALLOCATE stuCursor
--释放游标
IF LEN(@TempStuSql) > 1
BEGIN
SET @TempStuSql = SUBSTRING(@TempStuSql, 1, LEN(@TempStuSql) -1)
SET @StuSql = SUBSTRING(@StuSql, 1, LEN(@StuSql) - 1)
END
--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6
--sname,gender,mobile,address,email
DECLARE @stuTable NVARCHAR(20) --
IF @StuType = 1
SET @stuTable = 'Astudent'
ELSE
SET @stuTable = 'Bstudent'
IF LEN(@TempStuSql) > 1
BEGIN
-- 定义执行sql
DECLARE @insertSql NVARCHAR(max)
DECLARE @valueSql NVARCHAR(max)
DECLARE @sql NVARCHAR(max)
IF @type = 0 -- 一类学生
BEGIN
SET @insertSql = 'INSERT INTO ' + @stuTable + ' (id,Code,createTime,stuRefId,aStuType,aStuId,status,' + @StuSql + ') '
SET @valueSql = 'SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1, ' + @TempStuSql + ' FROM TempCustomer tc
LEFT JOIN Leads l ON l.TmpCustomerId = tc.id
WHERE l.ActivityID = ' + @stuId + ' AND (tc.BatchNo = ''' + Isnull(@stuNo, '') + ''' OR ''' + Isnull(@stuNo, '') + ''' = '''')
AND not exists (
SELECT 1 FROM t1 WHERE stuRefId = tc.id AND aStuType = ' + @type + ' AND aStuId = ' + @stuId + '
) AND not exists (
SELECT 1 FROM t2 WHERE stuRefId = tc.id AND aStuType = ' + @type + ' AND aStuId = ' + @stuId + '
) '
IF ISNULL(@tFlag, 1) = 0 --页面选择了学生id,直接升级选择的学生
SET @valueSql = @valueSql + 'AND tc.id IN (' + REPLACE(@stuClassIds,'@', ',') + ') '
SET @sql = @insertSql + @valueSql
EXEC sp_executesql @sql
END
ELSE IF @type = 1 -- 二类学生
BEGIN
SET @insertSql = 'INSERT INTO ' + @stuTable + ' (ID,Code,createTime,stuRefId,aStuType,aStuId,status,' + @StuSql + ') '
SET @valueSql = 'SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1, ' + @TempStuSql + '
FROM t2_' + @stuId + ' tc WHERE (SYS_BatchId = ''' + Isnull(@stuNo, '') + ''' OR ''' + Isnull(@stuNo, '') + ''' = '''')
AND not exists (
SELECT 1 FROM t1 WHERE stuRefId = tc.id AND aStuType = ' + @type + ' AND aStuId = ' + @stuId + '
) AND not exists (
SELECT 1 FROM t2 WHERE stuRefId = tc.id AND aStuType = ' + @type + ' AND aStuId = ' + @stuId + '
) '
IF ISNULL(@tFlag, 1) = 0 --页面选择了学生id,直接升级选择的学生
SET @valueSql = @valueSql + 'AND tc.id IN (' + REPLACE(@stuClassIds,'@', ',') + ') '
SET @sql = @insertSql + @valueSql
EXEC sp_executesql @sql
END
END
END
[b]2、优化后[/b]
ALTER PROCEDURE [dbo].[pro_demo]
@type varchar(10),
@stuId varchar(10),
@stuNo VARCHAR(20),
@tFlag varchar(1),
@stuClassIds varchar(max)
AS
BEGIN
SET NOCOUNT ON;
-- 定义游标,编辑sql, 取得字段对应关系
DECLARE @TempStuSql VARCHAR(2000)
DECLARE @StuSql VARCHAR(2000)
--create cursor
DECLARE stuCursor CURSOR FOR
SELECT TempCustomerField,CustomerField,CustomerType,IsOptionItem FROM UpgradeStuFieldDefine
WHERE ActType = @type
AND ActId = @stuId
AND LOWER(TempCustomerField) != 'id'
AND LOWER(CustomerField) != 'code'
ORDER BY ID DESC
--open cursor
OPEN stuCursor
DECLARE @TempCustomerField VARCHAR(5000)
DECLARE @CustomerField VARCHAR(100)
DECLARE @CustomerType VARCHAR(1)
DECLARE @IsOptionItem VARCHAR(1)
SET @TempStuSql = ''
SET @StuSql = ''
--fetch next from cursor
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TempStuSql = 'tc.' +@TempCustomerField + ',' + @TempStuSql
SET @StuSql = @CustomerField + ',' + @StuSql
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
--print '第1个打印'
--print @TempStuSql --tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,
--print @StuSql --customername,gender,mobile,address,email,
--close and deallocate cursor
CLOSE stuCursor
--@TempStuSql and @StuSql
IF LEN(@TempStuSql) > 1
BEGIN
SET @TempStuSql = SUBSTRING(@TempStuSql, 1, LEN(@TempStuSql) -1)
SET @StuSql = SUBSTRING(@StuSql, 1, LEN(@StuSql) - 1)
END
--@customerTable
DECLARE @customerTable NVARCHAR(20)
IF @CustomerType = 1
SET @customerTable = 'Astudent'
ELSE
SET @customerTable = 'Bstudent'
IF LEN(@TempStuSql) > 1
--main code #s
BEGIN
OPEN stuCursor
DECLARE @sql NVARCHAR(max) --last sql
DECLARE @insertSql NVARCHAR(max) --insert part
DECLARE @valueSql NVARCHAR(max) --value part
DECLARE @valueSql1 VARCHAR(2000) --START
DECLARE @valueSql2 VARCHAR(2000) --DYNAMIC COLUMN
DECLARE @valueSql3 VARCHAR(2000) --FROM
DECLARE @valueSql4 VARCHAR(2000) --LEFT JOIN
DECLARE @valueSql5 VARCHAR(2000) --END
SET @valueSql2=''
SET @valueSql4=''
IF @type = 0 -- Ma学生
BEGIN
SET @insertSql = 'INSERT INTO ' + @customerTable + ' (id,Code,createTime,upgradeCustomerRefId,upgradeActType,upgradeActId,status,' + @StuSql + ') '
SET @valueSql1='SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1'
SET @valueSql3=' FROM TempCustomer tc LEFT JOIN Leads l ON l.TmpCustomerId = tc.id '
SET @valueSql5=' WHERE l.ActivityID = ''' + @stuId + ''' AND (tc.BatchNo = ''' + Isnull(@stuNo, '') + ''' OR ''' + Isnull(@stuNo, '') + ''' = '''')
AND not exists ( SELECT 1 FROM customer WHERE upgradeCustomerRefId = tc.id AND upgradeActType = ' + @type + ' AND upgradeActId = ' + @stuId + ')
AND not exists ( SELECT 1 FROM company WHERE upgradeCustomerRefId = tc.id AND upgradeActType = ' + @type + ' AND upgradeActId = ' + @stuId + ') '
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsOptionItem='1'
BEGIN
SET @valueSql2=','+@TempCustomerField+'.id'+@valueSql2
SET @valueSql4=@valueSql4+' left join OptionItem '+@TempCustomerField+' on tc.'+@TempCustomerField+'='+@TempCustomerField+'.itemName '
END
ELSE
SET @valueSql2=',tc.'+@TempCustomerField+@valueSql2
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
CLOSE stuCursor
DEALLOCATE stuCursor
--print '打印left join=='
--print @valueSql4
END
ELSE IF @type = 1 -- Survey学生
BEGIN
SET @insertSql = 'INSERT INTO ' + @customerTable + ' (ID,Code,createTime,upgradeCustomerRefId,upgradeActType,upgradeActId,status,' + @StuSql + ') '
SET @valueSql1='SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1'
SET @valueSql3=' FROM T1_' + @stuId + ' tc '
SET @valueSql5=' WHERE (SYS_BatchId = ''' + Isnull(@stuNo, '') + ''' OR ''' + Isnull(@stuNo, '') + ''' = '''')
AND not exists ( SELECT 1 FROM T1 WHERE upgradeCustomerRefId = tc.id AND upgradeActType = ' + @type + ' AND upgradeActId = ' + @stuId + ')
AND not exists ( SELECT 1 FROM T2 WHERE upgradeCustomerRefId = tc.id AND upgradeActType = ' + @type + ' AND upgradeActId = ' + @stuId + ') '
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsOptionItem='1'
BEGIN
SET @valueSql2=','+@TempCustomerField+'.id'+@valueSql2
SET @valueSql4=@valueSql4+' left join OptionItem '+@TempCustomerField+' on tc.'+@TempCustomerField+'='+@TempCustomerField+'.itemName '
END
ELSE
SET @valueSql2=',tc.'+@TempCustomerField+@valueSql2
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
CLOSE stuCursor
DEALLOCATE stuCursor
END
IF LEN(@valueSql4)>1
SET @valueSql=@valueSql1+@valueSql2+@valueSql3+@valueSql4+@valueSql5 --@valueSql
ELSE
SET @valueSql=@valueSql1+@valueSql2+@valueSql3+@valueSql5
IF ISNULL(@tFlag, 1) = 0 --页面选择了学生id,直接升级选择的学生
SET @valueSql = @valueSql + 'AND tc.id IN (' + REPLACE(@stuClassIds,'@', ',') + ') ' --@valueSql
SET @sql = @insertSql + @valueSql
--print '打印'
--print @sql
EXEC sp_executesql @sql
END
--main code #e
END