If else in stored procedure sql server
Thank you all for your answers but I figured out how to do it and the final procedure looks like that :
Create Procedure sp_ADD_RESPONSABLE_EXTRANET_CLIENT
(
@ParLngId int output
)
as
Begin
if not exists (Select ParLngId from T_Param where ParStrIndex = 'RES' and ParStrP2 = 'Web')
Begin
INSERT INTO T_Param values('RES','¤ExtranetClient', 'ECli', 'Web', 1, 1, Null, Null, 'non', 'ExtranetClient', 'ExtranetClient', 25032, Null, '[email protected]', 'Extranet-Client', Null, 27, Null, Null, Null, Null, Null, Null, Null, Null, 1, Null, Null, 0 )
SET @ParLngId = @@IDENTITY
End
Else
Begin
SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
Return @ParLngId
End
End
So the thing that I found out and which made it works is:
if not exists
It allows us to use a boolean instead of Null or 0 or a number resulted of count()
If there are no matching row/s then @ParLngId
will be NULL
not zero, so you need IF @ParLngId IS NULL
.
You should also use SCOPE_IDENTITY()
rather than @@IDENTITY
.