获取存储过程output参数 急 在线等~~~~~~~

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

第一种方法: 使用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   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  



--这里ａ调ｂ的结果集，而ｂ中也有这样的应用ｂ调了ｃ的结果集，这是不允许的，

--会报“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







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/23/4584118.aspx

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.