日期:2014-05-18 浏览次数:20577 次
if object_id('p1','p') is not null drop proc p1 go create proc p1 as create table #t(id int) insert #t select 100 select * from #t go if object_id('p2','p') is not null drop proc p2 go create proc p2 as if object_id('tempdb..#t') is not null insert #t exec p1 else begin create table #t(id int) insert #t exec p1 end select * from #t go if object_id('p3','p') is not null drop proc p3 go create proc p3 as create table #t(id int) insert #t exec p2 select * from #t go exec p1 exec p2 exec p3 --INSERT EXEC 语句不能嵌套。
用临时表的方法, IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除 drop table #tmp select * into #tmp from tablename where 1=2 --创建临时表#tmp,其结构与tablename相同 declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) set @QueryString='select * from tablename ' insert into #tmp(field1,field2,...) exec(@querystirng)
------解决方案--------------------
学习。
------解决方案--------------------
那只能用存储过程调用存储过程了 参考一下吧 临时表没办法嵌套 考虑用output来做 第一种方法: 使用output参数 USE AdventureWorks; GO IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList; GO CREATE PROCEDURE Production.usp_GetList @product varchar(40) , @maxprice money , @compareprice money OUTPUT , @listprice money OUT AS SELECT p.name AS Product, p.ListPrice AS 'List Price' FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice; -- Populate the output variable @listprice. SET @listprice = (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.name LIKE @product AND p.ListPrice < @maxprice); -- Populate the output variable @compareprice. SET @compareprice = @maxprice; GO 另一个存储过程调用的时候: Create Proc Test as DECLARE @compareprice money, @cost money EXECUTE Production.usp_GetList '%Bikes%', 700, @compareprice OUT, @cost OUTPUT IF @cost <= @compareprice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.' 第二种方法:创建一个临时表 create proc GetUserName as begin select 'UserName' end Create table #tempTable (userName nvarchar(50)) insert into #tempTable(userName) exec GetUserName select #tempTable --用完之后要把临时表清空 drop table #tempTable--需要注意的是,这种方法不能嵌套。例如: procedure