sql中的输出参数 [英] out put parameters in sql

问题描述

我们可以将参数传递给sql server中的函数吗?
请给出原因.

Can we pass out parameters to functions in sql server?
please give reason.

推荐答案

请阅读！！！



1.返回码:始终是整数值.
2. OUTPUT参数:它可以返回数据(例如整数或字符值)或游标变量(游标是可以一次检索一行的结果集).
3.存储过程或存储过程调用的任何其他存储过程中包含的每个SELECT语句的结果集.
4.可以在存储过程之外引用的全局游标.

在存储过程中的参数上指定OUTPUT关键字可以将参数的值返回到调用程序.让我们使用AdventureWorks数据库通过一个简单的示例进行检查:

查看原图?

hi read this!!!!



1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program. Lets check this with a simple example by using AdventureWorks database:

view sourceprint?

USE [AdventureWorks]
GO
 
--// Create Stored Prcedure with OUTPUT parameter
CREATE PROCEDURE getContactName
    @ContactID INT,
    @FirstName VARCHAR(50) OUTPUT,
    @LastName  VARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @FirstName = FirstName, @LastName = LastName
    FROM Person.Contact
    WHERE ContactID = @ContactID
end
GO
--// Test the Procedure
DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)
--/ Test# 1
SET @CID = 100
EXEC getContactName @ContactID=@CID,
                    @FirstName=@FName OUTPUT,
                    @LastName=@LName OUTPUT
SELECT @FName as 'First Name', @LName as 'Last Name'
--/ Output
-- ContactID    First Name  Last Name
-- 100          Jackie      Blackwell
--/ Test# 2
SET @CID = 200
EXEC getContactName @ContactID=@CID,
                    @FirstName=@FName OUTPUT,
                    @LastName=@LName OUTPUT
SELECT @FName as 'First Name', @LName as 'Last Name'

--/ Output

-- ContactID    First Name  Last Name
-- 200          Martin      Chisholm

-//最终清理

--// Final Cleanup

DROP PROCEDURE getContactName
GO

否则，请参考以下链接:

http://sqlwithmanoj.wordpress.com/2011/03/23 /using-output-parameters-in-stored-procedures/ [ http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_%26_Return_Values [

otherwise refer this link:

http://sqlwithmanoj.wordpress.com/2011/03/23/using-output-parameters-in-stored-procedures/[^]

http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_%26_Return_Values[^]

regards
sarva

是的，SQL中的用户定义函数可以返回一个结果集或输出参数.

Yes, User Defined Function in SQL can return one Result Set or Output Parameter.

您可以使用函数的返回类型作为Out参数.

You can use return type ofthe function as a Out Parameter.

--Create a Function
Create Function SumOfTwoNumbers 
( 
@number1 Int, 
@number2 Int 
) 
Returns Int 
As 
Begin 
 Return(@number1 + @number2) 
End 
  
--Check Output
Select DBO.SumOfTwoNumbers (1, 2)  

这篇关于sql中的输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！