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 ?

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

Answer 2

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.