IF ELSE Stored Procedure

Question

Can anyone please point out what im doing wrong with this Stored Procedure please  I cant get it to compile and my software isnt giving any useful clues as to what is wrong with it   CREATE PROCEDURE web createSubscriptions          Member Id BIGINT      Trans type VARCHAR 100       Payment Status VARCHAR 100       Payment Date DATETIME      Trans Id VARCHAR 100        AS DECLARE  tmpType VARCHAR 15  BEGIN  INSERT INTO TBL SUBSCRIPTIONS  subs MemberID  subs Type  subs Status  subs DateGenerated  subs PaypalTransaction  VALUES  Member Id   Trans Type   Payment Status   Payment Date   Trans Id   IF  Trans type    subscr signup       BEGIN      tmpType    premium      END ELSE  Trans type    subscr cancel       BEGIN      tmpType    basic      END  UPDATE TBL MEMBERS SET members Type    tmpType WHERE members Id    Member Id  END

User · Accepted Answer

Nick is right  The next error is the else should be else if  you currently have a boolean expression in your else which makes no sense   Here is what it should be  ELSE IF  Trans type    subscr cancel       BEGIN     SET  tmpType    basic      END   You currently have the following  which is wrong    ELSE  Trans type    subscr cancel       BEGIN     SET  tmpType    basic      END   Here s a tip for the future- double click on the error and SQL Server management Studio will go to the line where the error resides  If you think SQL Server gives cryptic errors  which I don t think it does   then you haven t worked with Oracle

User · Answer

Just a tip for this  you don t need the BEGIN and END if it only contains a single statement   ie   IF  Trans type    subscr signup        set  tmpType    premium   ELSE iF  Trans type    subscr cancel          set     tmpType    basic

User · Answer

try   set  tmptype

User · Answer

try  IF  Trans type    subscr signup       BEGIN   set  tmpType    premium    END ELSE iF  Trans type    subscr cancel       begin      set     tmpType    basic      END

User · Answer

Try this with SQL join 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

yeah Nick is right   You need to use SET or SELECT to assign to  tmpType

User · Answer

It isn t giving any errors  Try SET  tmpType    premium  and SET  tmpType    basic

User · Answer

Are you missing the  SET  statement when assigning to your variables in the IF    ELSE block

[sql] IF/ELSE Stored Procedure

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to stored-procedures