저장 프로 시저에 선택적 OUTPUT 매개 변수를 사용할 수 있습니까?

저장 프로 시저에 선택적 OUTPUT 매개 변수를 사용할 수 있습니까?

---

여러 테이블에 값을 삽입하기 때문에 입력 및 출력 매개 변수가 많은 저장 프로 시저가 있습니다. 어떤 경우에는 저장된 proc이 입력 매개 변수에 따라 단일 테이블에만 삽입합니다. 다음은 설명을위한 모의 시나리오입니다.

테이블 / 데이터 개체 :

사람

Id
Name
Address

이름

Id
FirstName
LastName

주소

Id
Country
City

사람을 삽입하는 저장 프로 시저가 있다고 가정 해 보겠습니다. 주소가 존재하지 않으면 Address데이터베이스 의 테이블에 추가하지 않습니다 .

따라서 저장 프로 시저를 호출하는 코드를 생성 할 때 Address매개 변수를 추가하는 것을 귀찮게하고 싶지 않습니다 . 를 들어 INPUTSQL 서버 나 디폴트 값을 제공 할 수 있기 때문에 매개 변수이 괜찮습니다. 그러나 OUTPUT매개 변수의 경우 저장 프로 시저에서 무엇을해야 선택 사항으로 만들 수 있으므로 오류가 발생하지 않습니다.

프로 시저 또는 함수 'Person_InsertPerson'에는 제공되지 않은 '@AddressId'매개 변수가 필요합니다.

---

입력 및 출력 매개 변수 모두 기본값을 할당 할 수 있습니다. 이 예에서 :

CREATE PROCEDURE MyTest
  @Data1 int
 ,@Data2 int = 0
 ,@Data3 int = null output

AS

PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)

SET @Data3 = @Data3 + 1

RETURN 0

첫 번째 매개 변수는 필수이고 두 번째 및 세 번째 매개 변수는 선택 사항입니다. 호출 루틴에 의해 설정되지 않은 경우 기본값이 지정됩니다. 다른 값과 설정을 사용하여 SSMS에서 다음 테스트 호출 루틴과 함께 작동하는 방식을 확인하십시오.

DECLARE @Output int

SET @Output = 3

EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output

PRINT '---------'
PRINT @Output

---

출력 매개 변수와 기본값은 함께 잘 작동하지 않습니다! 이것은 SQL 10.50.1617 (2008 R2)에서 가져온 것입니다. 이 구조가 당신을 대신하여 그 가치에 마법처럼 작용 한다고 믿도록 속지 마십시오SET (동료가 그랬던 것처럼)!

이 "장난감"SP OUTPUT는 기본값인지 여부에 관계없이 매개 변수 값을 조사합니다 NULL.

CREATE PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
RETURN

당신이 초기화되지 않은 값 (즉에 보낼 경우 NULL용) OUTPUT, 당신은 정말 가지고 NULL는 SP 내부, 그리고 0. 그 매개 변수에 대해 무언가 전달되었습니다.

declare @QR int
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

출력은 다음과 같습니다.

wtf its NULL
@QR=NULL

SET호출자로부터 명시 적을 추가 하면 다음을 얻습니다.

declare @QR int
set @QR = 999
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

그리고 (놀랍지 않은) 출력 :

@QR=999

다시 말하지만, 매개 변수가 전달되고 SP는 SET값 에 대해 명시적인 조치를 취하지 않았습니다 .

Add a SET of the OUTPUT parameter in the SP (like you're supposed to do), but do not set anything from the caller:

ALTER PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
SET @QtyRetrieved = @Qty
RETURN

Now when executed:

declare @QR int
exec [dbo].[omgwtf] 1234, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

the output is:

wtf its NULL
@QR=1234

This is the "standard" behavior for OUTPUT parameter handling in SPs.

Now for the plot twist: The only way to get the default value to "activate", is to not pass the OUTPUT parameter at all, which IMHO makes little sense: since it's set up as an OUTPUT parameter, that would mean returning something "important" that should be collected.

declare @QR int
exec [dbo].[omgwtf] 1
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

gives this output:

yay its zero
@QR=NULL

But this fails to capture the SPs output, presumably the purpose of that SP to begin with.

IMHO this feature combination is a dubious construct I would consider a code smell (phew!!)

---

Looks like I can just add a default value to the OUTPUT parameter such as:

@AddressId int = -1 Output

Seems like its poor in terms of readability since AddressId is intended strictly as an OUTPUT variable. But it works. Please let me know if you have a better solution.

---

Adding on to what Philip said:

I had a stored procedure in my sql server database that looked like the following:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) OUTPUT)

And I was calling it from my .net code as the following:

 DataTable dt = SqlClient.ExecuteDataTable(<connectionString>, <storedProcedure>);

I was getting an System.Data.SqlClient.SqlException: Procedure or function expects parameter '@current_phase', which was not supplied.

I am also using this function somewhere else in my program and passing in a parameter and handling the output one. So that I didn't have to modify the current call I was making I just changed the stored procedure to make the output parameter also optional.

So it now looks as the following:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) = NULL OUTPUT)

---

Since you are executing a stored procedure and not a SQL statement, you have to set the command type of your SQL Command to Stored Procedure:

cmd.CommandType = CommandType.StoredProcedure;

Taken from here.

Also, once you get that error removed, you can use SQL's nvl() function in your procedure to specify what you want displayed when a NULL value is encountered.

Sorry about not properly addressing the question...must have misunderstood you. Here's an example of nvl, which I think might address it a little better?

select NVL(supplier_city, 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

참고URL : https://stackoverflow.com/questions/3267523/can-i-have-an-optional-output-parameter-in-a-stored-procedure