第一种方法: 使用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 pJOIN 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 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
======================================================================
D. 使用 OUTPUT 参数
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。
首先,创建过程:
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
接下来,将该 OUTPUT 参数用于控制流语言。
说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。
参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。
DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
下面是结果集:
Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
===========================================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[addproduct]
@productId int output,
@productName varchar(50),
@productSize varchar(50),
@productColor varchar(20),
@price money,
@type int,
@productDesc text,
@imageUrl varchar(200)
AS
BEGIN TRANSACTION
DECLARE @errornum INT
declare @id int
SET @errornum =0
--调用存储过程UP_product_ADD--
set @id=(exec UP_product_ADD @productName,@productSize,@productColor,@price,@type,@productDesc,@productId output)
set @errornum=@errornum+@@error
--将存储过程UP_product_ADD的返回值 作为参数
insert into IMG (ImageUrl, productId) values(@imageUrl,@id)
set @errornum=@errornum+@@error
IF @errornum <> 0
BEGIN
PRINT '插入失败'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '成功插入'
COMMIT TRANSACTION
END