第一种方法: 使用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
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 ))) + ' . '
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
as
begin
select ' UserName '
end
Create table #tempTable (userName nvarchar ( 50 ))
insert into #tempTable(userName)
exec GetUserName
select #tempTable
-- 用完之后要把临时表清空
drop table #tempTable
--
需要注意的是,这种方法不能嵌套。例如:
procedure a
begin
...
insert # table exec b
end
procedure b
begin
...
insert # table exec c
select * from # table
end
procedure c
begin
...
select * from sometable
end
-- 这里a调b的结果集,而b中也有这样的应用b调了c的结果集,这是不允许的,
-- 会报“INSERT EXEC 语句不能嵌套”错误。在实际应用中要避免这类应用的发生。
procedure a
begin
...
insert # table exec b
end
procedure b
begin
...
insert # table exec c
select * from # table
end
procedure c
begin
...
select * from sometable
end
-- 这里a调b的结果集,而b中也有这样的应用b调了c的结果集,这是不允许的,
-- 会报“INSERT EXEC 语句不能嵌套”错误。在实际应用中要避免这类应用的发生。
第三种方法:声明一个变量,用exec(@sql)执行:
1);EXEC 执行SQL语句
declare
@rsql
varchar
(
250
)
declare @csql varchar ( 300 )
declare @rc nvarchar ( 500 )
declare @cstucount int
declare @ccount int
set @rsql = ' (select Classroom_id from EA_RoomTime where zc= ' + @zc + ' and xq= ' + @xq + ' and T ' + @time + ' = '' 否 '' ) and ClassroomType= '' 1 '''
-- exec(@rsql)
set @csql = ' select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '
set @rc = @csql + @rsql
exec sp_executesql @rc ,N ' @a int output,@b int output ' , @cstucount output, @ccount output -- 将exec的结果放入变量中的做法
-- select @csql+@rsql
-- select @cstucount
declare @csql varchar ( 300 )
declare @rc nvarchar ( 500 )
declare @cstucount int
declare @ccount int
set @rsql = ' (select Classroom_id from EA_RoomTime where zc= ' + @zc + ' and xq= ' + @xq + ' and T ' + @time + ' = '' 否 '' ) and ClassroomType= '' 1 '''
-- exec(@rsql)
set @csql = ' select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '
set @rc = @csql + @rsql
exec sp_executesql @rc ,N ' @a int output,@b int output ' , @cstucount output, @ccount output -- 将exec的结果放入变量中的做法
-- select @csql+@rsql
-- select @cstucount