SQL新手,简单的存储过程,编译时没有错误,但是在执行的时候有2个错误....SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
/*************************************************************
Author: Kevin Ge
DATE: 2010/07/19
Description:
*************************************************************/ALTER PROCEDURE [dbo].[InsertSendingGroup]
AS
DECLARE @ID VARCHAR(40) ,
@GROUP_NAME VARCHAR(120) ,--组名称
@GROUP_CODE VARCHAR(50) ,--组代码
@SQL VARCHAR(MAX) ,--M_GROUP里查询出来的SQL
@CONTROL_SQL VARCHAR(MAX) ,--控制发送量的SQL
@CON_SQL VARCHAR(MAX) ,--将M_GROUP查询出来的SQL创建游标
@DELETE_SQL VARCHAR(MAX) ,--拼接删除的SQL
@CON_ID VARCHAR(50) ,--联系人ID
@CAP_ID VARCHAR(50) ;--营销活动ID
BEGIN
SET @ID = ''
SET @GROUP_NAME = ''
SET @GROUP_CODE = ''
SET @SQL = ''
TRUNCATE TABLE m_group_sending --将m_group_sending进行清空处理
DECLARE CAMP_LIST CURSOR FORWARD_ONLY FOR SELECT dbo.M_GROUP.ID,dbo.M_GROUP.GROUP_NAME,dbo.M_GROUP.GROUP_CODE,DBO.M_GROUP.CAMPAGIN_ID,DBO.M_GROUP.SENDINGGROUP_SCRIPT,DBO.M_GROUP.GROUP_SCRIPT FROM dbo.M_GROUP FOR READ ONLY ;--M_GROUP游标创建CAMP_LIST
OPEN CAMP_LIST --打开游标CAMP_LIST
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--获得CAMP_LIST第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CON_SQL = 'DECLARE INSERT_CAMP CURSOR FORWARD_ONLY FOR '
+ @SQL + ' FOR READ ONLY ' --根据查询出来的SQL语句拼接内层游标SQL
EXEC (@CON_SQL)--创建游标INSERT_CAMP
OPEN INSERT_CAMP--打开游标INSERT_CAMP
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--获得游标INSERT_CAMP第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CON_ID IS NOT NULL
INSERT INTO M_GROUP_SENDING--插入M_GROUP_SENDING中
( GROUP_ID ,
GROUP_NAME ,
GROUP_CODE ,
CAMP_ID ,
CON_ID
)
VALUES ( @ID ,
@GROUP_NAME ,
@GROUP_CODE ,
@CAP_ID ,
@CON_ID
)
ELSE
SET @CON_ID = 'NULL' ;
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--游标INSERT_CAMP下一记录
END
CLOSE INSERT_CAMP--关闭游标INSERT_CAMP
DEALLOCATE INSERT_CAMP
IF @CONTROL_SQL IS NOT NULL
SET @DELETE_SQL = 'DELECT FROM M_GROUP_SENDING WHERE CON_ID IN ('
+ @CONTROL_SQL + ')'
ELSE
SET @DELETE_SQL = 'SELECT COUNT(*) FROM M_GROUP_SENDING'
EXEC(@DELETE_SQL)
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--游标CAMP_LIST的下一记录
END
CLOSE CAMP_LIST--关闭游标CAMP_LIST
DEALLOCATE CAMP_LISt
COMMIT
END
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。(1 行受影响)
blablabla.....
(1 行受影响)
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 3902,级别 16,状态 1,过程 InsertSendingGroup,第 70 行
COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。谢谢各位~~
SET QUOTED_IDENTIFIER ON
go
/*************************************************************
Author: Kevin Ge
DATE: 2010/07/19
Description:
*************************************************************/ALTER PROCEDURE [dbo].[InsertSendingGroup]
AS
DECLARE @ID VARCHAR(40) ,
@GROUP_NAME VARCHAR(120) ,--组名称
@GROUP_CODE VARCHAR(50) ,--组代码
@SQL VARCHAR(MAX) ,--M_GROUP里查询出来的SQL
@CONTROL_SQL VARCHAR(MAX) ,--控制发送量的SQL
@CON_SQL VARCHAR(MAX) ,--将M_GROUP查询出来的SQL创建游标
@DELETE_SQL VARCHAR(MAX) ,--拼接删除的SQL
@CON_ID VARCHAR(50) ,--联系人ID
@CAP_ID VARCHAR(50) ;--营销活动ID
BEGIN
SET @ID = ''
SET @GROUP_NAME = ''
SET @GROUP_CODE = ''
SET @SQL = ''
TRUNCATE TABLE m_group_sending --将m_group_sending进行清空处理
DECLARE CAMP_LIST CURSOR FORWARD_ONLY FOR SELECT dbo.M_GROUP.ID,dbo.M_GROUP.GROUP_NAME,dbo.M_GROUP.GROUP_CODE,DBO.M_GROUP.CAMPAGIN_ID,DBO.M_GROUP.SENDINGGROUP_SCRIPT,DBO.M_GROUP.GROUP_SCRIPT FROM dbo.M_GROUP FOR READ ONLY ;--M_GROUP游标创建CAMP_LIST
OPEN CAMP_LIST --打开游标CAMP_LIST
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--获得CAMP_LIST第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CON_SQL = 'DECLARE INSERT_CAMP CURSOR FORWARD_ONLY FOR '
+ @SQL + ' FOR READ ONLY ' --根据查询出来的SQL语句拼接内层游标SQL
EXEC (@CON_SQL)--创建游标INSERT_CAMP
OPEN INSERT_CAMP--打开游标INSERT_CAMP
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--获得游标INSERT_CAMP第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CON_ID IS NOT NULL
INSERT INTO M_GROUP_SENDING--插入M_GROUP_SENDING中
( GROUP_ID ,
GROUP_NAME ,
GROUP_CODE ,
CAMP_ID ,
CON_ID
)
VALUES ( @ID ,
@GROUP_NAME ,
@GROUP_CODE ,
@CAP_ID ,
@CON_ID
)
ELSE
SET @CON_ID = 'NULL' ;
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--游标INSERT_CAMP下一记录
END
CLOSE INSERT_CAMP--关闭游标INSERT_CAMP
DEALLOCATE INSERT_CAMP
IF @CONTROL_SQL IS NOT NULL
SET @DELETE_SQL = 'DELECT FROM M_GROUP_SENDING WHERE CON_ID IN ('
+ @CONTROL_SQL + ')'
ELSE
SET @DELETE_SQL = 'SELECT COUNT(*) FROM M_GROUP_SENDING'
EXEC(@DELETE_SQL)
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--游标CAMP_LIST的下一记录
END
CLOSE CAMP_LIST--关闭游标CAMP_LIST
DEALLOCATE CAMP_LISt
COMMIT
END
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。(1 行受影响)
blablabla.....
(1 行受影响)
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 156,级别 15,状态 1,第 1 行
关键字 'FROM' 附近有语法错误。
消息 3902,级别 16,状态 1,过程 InsertSendingGroup,第 70 行
COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。谢谢各位~~
SET QUOTED_IDENTIFIER ON
go
/*************************************************************
Author: Kevin Ge
DATE: 2010/07/19
Description:
*************************************************************/ALTER PROCEDURE [dbo].[InsertSendingGroup]
AS
DECLARE @ID VARCHAR(40) ,
@GROUP_NAME VARCHAR(120) ,--组名称
@GROUP_CODE VARCHAR(50) ,--组代码
@SQL VARCHAR(MAX) ,--M_GROUP里查询出来的SQL
@CONTROL_SQL VARCHAR(MAX) ,--控制发送量的SQL
@CON_SQL VARCHAR(MAX) ,--将M_GROUP查询出来的SQL创建游标
@DELETE_SQL VARCHAR(MAX) ,--拼接删除的SQL
@CON_ID VARCHAR(50) ,--联系人ID
@CAP_ID VARCHAR(50) ;--营销活动ID
BEGIN
SET @ID = ''
SET @GROUP_NAME = ''
SET @GROUP_CODE = ''
SET @SQL = ''
TRUNCATE TABLE m_group_sending --将m_group_sending进行清空处理
DECLARE CAMP_LIST CURSOR FORWARD_ONLY FOR SELECT dbo.M_GROUP.ID,dbo.M_GROUP.GROUP_NAME,dbo.M_GROUP.GROUP_CODE,DBO.M_GROUP.CAMPAGIN_ID,DBO.M_GROUP.SENDINGGROUP_SCRIPT,DBO.M_GROUP.GROUP_SCRIPT FROM dbo.M_GROUP FOR READ ONLY ;--M_GROUP游标创建CAMP_LIST
OPEN CAMP_LIST --打开游标CAMP_LIST
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--获得CAMP_LIST第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CON_SQL = 'DECLARE INSERT_CAMP CURSOR FORWARD_ONLY FOR '
+ @SQL + ' FOR READ ONLY ' --根据查询出来的SQL语句拼接内层游标SQL
EXEC (@CON_SQL)--创建游标INSERT_CAMP
OPEN INSERT_CAMP--打开游标INSERT_CAMP
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--获得游标INSERT_CAMP第一条记录
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CON_ID IS NOT NULL
INSERT INTO M_GROUP_SENDING--插入M_GROUP_SENDING中
( GROUP_ID ,
GROUP_NAME ,
GROUP_CODE ,
CAMP_ID ,
CON_ID
)
VALUES ( @ID ,
@GROUP_NAME ,
@GROUP_CODE ,
@CAP_ID ,
@CON_ID
)
ELSE
SET @CON_ID = 'NULL' ;
FETCH NEXT FROM INSERT_CAMP INTO @CON_ID--游标INSERT_CAMP下一记录
END
CLOSE INSERT_CAMP--关闭游标INSERT_CAMP
DEALLOCATE INSERT_CAMP
IF @CONTROL_SQL IS NOT NULL
SET @DELETE_SQL = 'DELETE FROM M_GROUP_SENDING WHERE CON_ID IN ('
+ @CONTROL_SQL + ')'
ELSE
SET @DELETE_SQL = 'SELECT COUNT(*) FROM M_GROUP_SENDING'
EXEC(@DELETE_SQL)
FETCH NEXT FROM CAMP_LIST INTO @ID,@GROUP_NAME,@GROUP_CODE,@CAP_ID,@SQL,@CONTROL_SQL--游标CAMP_LIST的下一记录
END
CLOSE CAMP_LIST--关闭游标CAMP_LIST
DEALLOCATE CAMP_LISt
END
print @CON_SQL 出来看看效果COMMIT没有事务,怎么commit/*启动一个事务*/
begin transaction
/*如果有出错就回滚*/
if(@@error<>0)
begin
rollback transaction
end
/*提交*/
commit transaction