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         lt    lt         Or when used as an expression    Somebody has a solution

User · Accepted Answer

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

User · Answer

Try this one -  CREATE PROCEDURE sp ADD USER EXTRANET CLIENT INDEX PHY AS BEGIN      DECLARE  ParLngId INT     SELECT TOP 1  ParLngId   ParLngId     FROM dbo T Param     WHERE ParStrNom    Extranet Client       IF   ParLngId   0      BEGIN         INSERT INTO dbo 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           RETURN SCOPE IDENTITY       END     ELSE BEGIN          RETURN  ParLngId       END  END

User · Answer

Instead of writing   Select top 1 ParLngId from T Param where ParStrNom    Extranet Client    Write   Select top 1 ParLngId from T Param where ParStrNom IN  Extranet Client    i e  replace     sign by  IN

User · Answer

You can try below Procedure Sql   Create Procedure sp ADD USER EXTRANET CLIENT INDEX PHY    ParLngId int output   as Begin     -- Min will return only 1 value  if  Extranet Client  is found     -- IsNull will take care of  Extranet Client  not found  returning 0 instead of Null     -- But T Param must be a Master Table with ParStrNom having a Unique Index  if so Min is not reqd at all     -- But  PHY    Extranet Client  suggests that Unique Key has 2 columns  not just ParStrNom      SET  ParLngId   IsNull  Select Min  ParLngId  from T Param where ParStrNom    Extranet Client    0        -- Nothing changed below      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

User · Answer

Try this with join query statements  CREATE PROCEDURE  dbo   deleteItem   ItemId int   0  AS  Begin  DECLARE  cnt int   SET NOCOUNT ON SELECT  cnt  COUNT ttm Id       from ItemTransaction itr INNER JOIN ItemUnitMeasurement ium          ON itr Id   ium ItemTransactionId  INNER JOIN ItemMaster im          ON itr ItemId   im Id INNER JOIN TransactionTypeMaster ttm          ON itr TransactionTypeMasterId   ttm Id          where im Id    ItemId  if  cnt   1      Begin     DECLARE  transactionType varchar 255       DECLARE  mesurementAmount float      DECLARE  itemTransactionId int      DECLARE  itemUnitMeasurementId int           SELECT  transactionType   ttm TransactionType    mesurementAmount   ium Amount   itemTransactionId   itr Id   itemUnitMeasurementId   ium Id         from ItemTransaction itr INNER JOIN ItemUnitMeasurement ium              ON itr Id   ium ItemTransactionId INNER JOIN TransactionTypeMaster ttm              ON itr TransactionTypeMasterId   ttm Id              where itr ItemId    ItemId           if  transactionType    Close  and  mesurementAmount   0              Begin                 delete from ItemUnitMeasurement where Id    itemUnitMeasurementId               End         else             Begin                 delete from ItemTransaction where Id    itemTransactionId              End     End else  Begin     delete from ItemMaster where Id    ItemId   End END

User · Answer

You do not have to have the RETURN stament   Have anther look at Using a Stored Procedure with Output Parameters  Also have another look at the OUT section in CREATE PROCEDURE

User · Answer

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

User · Answer

if not exists  select dist id from tbl stock where dist id   cust id and item id  item id      insert into tbl stock dist id item id qty values  cust id   item id   qty    else     update tbl stock set qty  qty    qty  where dist id   cust id and item id   item id

[sql-server] If else in stored procedure sql server

Examples related to sql-server

Examples related to stored-procedures