[SQL] stored procedure met null parameters

[Goldme]

Ik heb een stored procedure die veel parameters heeft. Nu is het zo dat niet alle parameters opgegeven hoeven te zijn. Wat ik dan doe bij het aanmaken van de Command object(VB.NET) is weliswaar de parameter toevoegen aan de lijst met parameters maar er geen value aan toe te wijzen voor die parameter die niet is opgegeven.

Dat vindt SQL niet leuk! de volgende foutmelding komt tevoorschijn bij het runnen van de command vanauit de applicatie:

Procedure 'spInsertRecord' expects parameter '@Password', which was not supplied

Wat is de beste manier om in de stored procedure om te gaan met niet gespecificeerde parameters? Hieronder zie je de simpele versie van de code. In werkelijkheid heb ik 16 parameters die doorgegeven worden aan de SP. SQL vindt het dus niet leuk als ik ServiceVersion bijv. toevoeg aan de lijst met parameters maar niet specificeer!

SQL:

CREATE PROCEDURE DBO.spInsertRecord
(
    @ServiceName varchar(100),
    @ServiceVersion varchar(50)
)
AS
    SET NOCOUNT OFF
INSERT INTO dbo.tblRequests(serviceName, serviceVersion) VALUES (@ServiceName, @ServiceVersion);

GO

[ Voor 3% gewijzigd door Goldme op 13-01-2005 10:55 ]

[P_de_B]

Als je het zo doet:

REATE PROCEDURE DBO.spInsertRecord
(
@ServiceName varchar(100) = defaultwaarde,
@ServiceVersion varchar(50)
)
AS


hoef je niet de parameters te specificeren. Anders moet je ze expliciet de DBNull value geven

[whoami]

Je kan zowiezo waarden als DBNull.Value meegeven aan de parameters die niet moeten opgevuld worden.

Als je ook SELECT queries hebt met optionele zoekcriteria, dan zou ik niet met SP's werken, maar met met dynamisch opgebouwde parametrized queries in de client.

In .NET zal je zowiezo bij je stored procedure alle parameters aan het SqlDbCommand moeten meegeven.
Je zult dus alle parameters moeten adden, en bij het 'adden', kan je al een default-waarde opgeven.
bv:

code:

myInsertCommand.Parameters.Add ("@p_Param1", SqlDbType.Int).Value = DBNull.Value;
myInsertCommand.Parameters.Add ("@p_Param2", SqlDbType.Varchar).Value = DBNull.Value;
.....

Zo heb je nu iedere parameter die de SP verwacht toegevoegd en al van een NULL value voorzien.
Nu kan je nog de waarden van die parameters gaan aanpassen voor die parameters waar je wel een waarde wilt aan geven.

[ Voor 55% gewijzigd door whoami op 13-01-2005 11:00 ]

[TeeDee]

Misschien ook wel interessant om te lezen: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Dan ben je volgens mij van het "gezeur" af, omdat je juist geen SP's gebruikt.

[whoami]

TeeDee schreef op donderdag 13 januari 2005 @ 10:59:

Dan ben je volgens mij van het "gezeur" af, omdat je juist geen SP's gebruikt.

Geen SP's gebruiken is niet altijd een optie. (Bv een enterprise environment waar alles via SP's moet gebeuren (company policy)).

[Tukk]

TeeDee schreef op donderdag 13 januari 2005 @ 10:59:
Misschien ook wel interessant om te lezen: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Dan ben je volgens mij van het "gezeur" af, omdat je juist geen SP's gebruikt.

Der zijn ook wel redenen om SP te gebruiken, ondanks de tegenredenen die hier staan vermeld.

Voorbeeld: Rapportage tools, een rapportage tool verwacht 1 dataset terug, dat is heel grote query, of meerdere in een SP die 1 dataset terug geeft.

uit ervaring: SP's hebben zeker nut.

[TeeDee]

@ Tukk, klopt. Maar na het lezen van dit artikel probeer ik ze gewoon te vermijden.

whoami schreef op donderdag 13 januari 2005 @ 11:01:
[...]
Geen SP's gebruiken is niet altijd een optie. (Bv een enterprise environment waar alles via SP's moet gebeuren (company policy)).

Uiteraard. Als ik het artikel goed begrijp (zoniet, dan kan Efbe misschien opheldering geven) dien je SP's alleen te gebruiken in zaken als:

• Company policy (zoals jij zegt)
• Als het een significante performance boost geeft.

Een voorbeeld: Ik had een webapp, die diende als tool voor een groot bedrijf om te zien wat de status was van een order.
Tevens moest er een totaal van alle orders, types komen. Gesorteerd op maand, jaar. Gegroepeerd op type, besteldag en bezorgdatum.

Doordat de structuur ruk was, duurde het ongeveer 1 dag en toen ging de handel plat door te hoge load. Door her een der een SP in combinatie met een View te gebruiken kon de load aanzienlijk verlaagd worden en waren de crashes verleden tijd.

Maar goed, dit is redelijk offtopic. Excuus daarvoor

[ Voor 5% gewijzigd door TeeDee op 13-01-2005 11:08 ]