我在一本上看到这么一个语句set @sql= 'select * into #tableTemp01 from '+@tableName+@crossTempTableStr+' '+@whereStr+' '+@orderByStr+' '+
'alter table #tableTemp01 add tableTempID int IDENTITY (1, 1) '+
'select * into #tableTemp02 from #tableTemp01 '+
'set rowcount '+convert(varchar(10),@totalRow)+' '+
'select * into #tableTemp03 from #tableTemp02 order by tableTempID '+
'set rowcount '+convert(varchar(10),@pageSize)+' '+
'select * into #tableTemp04 from #tableTemp03 order by tableTempID desc '+
'select * from #tableTemp04 order by tableTempID'
execute sp_executesql @sql这个@sql是不是包含了6个独立语句啊,其中5个查询,1个更新。execute sp_executesql @sql 会怎么样执行呢,他认得出这是6个语句会分6次执行吗?还是一次可以执行多个语句?
'alter table #tableTemp01 add tableTempID int IDENTITY (1, 1) '+
'select * into #tableTemp02 from #tableTemp01 '+
'set rowcount '+convert(varchar(10),@totalRow)+' '+
'select * into #tableTemp03 from #tableTemp02 order by tableTempID '+
'set rowcount '+convert(varchar(10),@pageSize)+' '+
'select * into #tableTemp04 from #tableTemp03 order by tableTempID desc '+
'select * from #tableTemp04 order by tableTempID'
execute sp_executesql @sql这个@sql是不是包含了6个独立语句啊,其中5个查询,1个更新。execute sp_executesql @sql 会怎么样执行呢,他认得出这是6个语句会分6次执行吗?还是一次可以执行多个语句?
set @sql= 'select * into #tableTemp01 from '+@tableName+@crossTempTableStr+' '+@whereStr+' '+@orderByStr+' '+
'alter table #tableTemp01 add tableTempID int IDENTITY (1, 1) '+
'select * into #tableTemp02 from #tableTemp01 '+
'set rowcount '+convert(varchar(10),@totalRow)+' '+
'select * into #tableTemp03 from #tableTemp02 order by tableTempID '+
'set rowcount '+convert(varchar(10),@pageSize)+' '+
'select * into #tableTemp04 from #tableTemp03 order by tableTempID desc '+
'select * from #tableTemp04 order by tableTempID' execute sp_executesql @sql 我在查询分析器里面调试它的时候提示错误:在多语句事务内不允许使用 ALTER DATABASE 语句。而当我在查询分析器里用exec 执行该存储过程是又是可以正确得到结果的,并且没有任何错误提示。请高手指教。
set @sql= 'select * into #tableTemp01 from '+@tableName+@crossTempTableStr+' '+@whereStr+' '+@orderByStr+' '+
'go --加个go
alter table #tableTemp01 add tableTempID int IDENTITY (1, 1) '+
'select * into #tableTemp02 from #tableTemp01 '+
'set rowcount '+convert(varchar(10),@totalRow)+' '+
'select * into #tableTemp03 from #tableTemp02 order by tableTempID '+
'set rowcount '+convert(varchar(10),@pageSize)+' '+
'select * into #tableTemp04 from #tableTemp03 order by tableTempID desc '+
'select * from #tableTemp04 order by tableTempID'
不行啊。
不仅“在多语句事务内不允许使用 ALTER DATABASE 语句。”的错误提示依旧出现,而且连 在查询分析器里用exec 执行该存储过程也出现错误
“服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'go' 附近有语法错误。”