Kan ik een optionele OUTPUT-parameter hebben in een opgeslagen procedure?

Ik heb een opgeslagen procedure die een heleboel invoer- en uitvoerparameters heeft, omdat het waarden invoegen in meerdere tabellen is. In sommige gevallen wordt het opgeslagen proces alleen ingevoegd in een enkele tabel (afhankelijk van de invoerparameters). Hier is een nagebootst scenario om te illustreren.

Tabellen / gegevensobjecten:

Persoon

Id
Name
Address

Naam

Id
FirstName
LastName

Adres

Id
Country
City

Stel dat ik een opgeslagen procedure heb die een persoon invoegt. Als het adres niet bestaat, zal ik het niet toevoegen aan de Address-tabel in de database.

Dus wanneer ik de code genereer om de opgeslagen procedure aan te roepen, wil ik niet de moeite nemen om de parameter Addresstoe te voegen. Voor INPUT-parameters is dit oké omdat ik met SQL Server standaardwaarden kan opgeven. Maar voor de parameter OUTPUTwat moet ik doen in de opgeslagen procedure om het optioneel te maken, zodat ik geen foutmelding krijg…

Procedure of functie ‘Person_InsertPerson’ verwacht parameter
‘@AddressId’, die niet is opgegeven.

Antwoord 1, autoriteit 100%

Zowel invoer- als uitvoerparameters kunnen standaard worden toegewezen. In dit voorbeeld:

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

de eerste parameter is vereist, en de tweede en derde zijn optioneel – indien niet ingesteld door de aanroepende routine, zullen ze de standaardwaarden toegewezen krijgen. Probeer ermee te rommelen en de volgende testoproeproutine in SSMS met verschillende waarden en instellingen om te zien hoe het allemaal samenwerkt.

DECLARE @Output int
SET @Output = 3
EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output
PRINT '---------'
PRINT @Output

Antwoord 2, autoriteit 11%

Uitvoerparameters en standaardwaarden werken niet goed samen! Dit komt uit SQL 10.50.1617 (2008 R2). Laat u niet misleidendoor te geloven dat deze constructie op magische wijze namens u een SETnaar die waarde doet (zoals mijn collega deed)!

Deze “speelgoed” SP ondervraagt ​​de parameterwaarde OUTPUT, of het nu de standaardwaarde is of 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

Als je een niet-geïnitialiseerde waarde (dwz NULL) voor de OUTPUTinstuurt, heb je echt NULLbinnen de SP, en niet 0. Logisch, er is iets doorgegeven voor die parameter.

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

uitvoer is:

wtf its NULL
@QR=NULL

Als we een expliciete SETvan de beller toevoegen, krijgen we:

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

en de (niet verrassende) output:

@QR=999

Nogmaals, logisch, een parameter is doorgegeven en SP heeft geen expliciete actie ondernomen om een ​​waarde te SET.

Voeg een SETvan de OUTPUTparameter toe in de SP (zoals je zou moeten doen), maar stel niets in van de beller:

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

Nu wanneer uitgevoerd:

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

de uitvoer is:

wtf its NULL
@QR=1234

Dit is het “standaard” gedrag voor het afhandelen van OUTPUTparameters in SP’s.

Nu voor de plot twist: de enige manier om de standaardwaarde op “activeren” te krijgen, is door de parameter OUTPUThelemaal niet door te geven, wat IMHO weinig zin heeft: aangezien het is ingesteld als een OUTPUT-parameter, zou dat betekenen dat er iets “belangrijks” wordt geretourneerd dat moet worden verzameld.

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

geeft deze output:

yay its zero
@QR=NULL

Maar dit kan de output van de SP niet vastleggen, vermoedelijk het doel van die SP om mee te beginnen.

IMHO deze combinatie van functies is een twijfelachtige constructie die ik zou beschouwen als een code-geur(pfff!!)

Antwoord 3, autoriteit 3%

Het lijkt erop dat ik gewoon een standaardwaarde kan toevoegen aan de parameter OUTPUT, zoals:

@AddressId int = -1 Output

Het lijkt erop dat het slecht leesbaar is, aangezien AddressIdstrikt bedoeld is als een OUTPUT-variabele. Maar het werkt. Laat het me weten als je een betere oplossing hebt.

Antwoord 4

Aanvullend op wat Philip zei:

Ik had een opgeslagen procedure in mijn SQL-serverdatabase die er als volgt uitzag:

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

En ik noemde het vanuit mijn .net-code als volgt:

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

Ik kreeg een System.Data.SqlClient.SqlException: procedure of functie verwacht parameter ‘@current_phase’, die niet is opgegeven.

Ik gebruik deze functie ook ergens anders in mijn programma en geef een parameter door en behandel de uitvoer. Zodat ik de huidige aanroep die ik deed niet hoefde te wijzigen, ik heb zojuist de opgeslagen procedure gewijzigd om de uitvoerparameter ook optioneel te maken.

Dus het ziet er nu als volgt uit:

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