If else in stored procedure sql server

Question

I have created a stored procedure as follow:

Create Procedure sp_ADD_USER_EXTRANET_CLIENT_INDEX_PHY
(
@ParLngId int output
)
as
Begin
    SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
    if(@ParLngId = 0)
        begin
            Insert Into T_Param values ('PHY', 'Extranet Client', Null, Null, 'T', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)
            SET @ParLngId = @@IDENTITY
        End
    Return @ParLngId
End

So I set a variable @ParLngId, I check if there is a such data in a table, if yes, I return the value, if not I insert one and return the variable which contains the Id of the inserted line... But now it shows me an SqlException:

Subquery returned more values. This is not permitted when the subquery follows =,! =, <, <=,>,> = Or when used as an expression.

Somebody has a solution ?

question from:https://stackoverflow.com/questions/18532663/if-else-in-stored-procedure-sql-server

Answer 1

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()