views:

108

answers:

7

Hi,

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
+7  A: 

It isn't giving any errors? Try
SET @tmpType = 'premium'
and
SET @tmpType = 'basic'

Nick
I just tried that and it returns the following unhelpful errorIncorrect syntax near '@Trans_type'.
Munklefish
That's definitely part of the problem
RichardOD
My answer has the next fix. :-)
RichardOD
+1  A: 

try

set @tmptype
HLGEM
I just tried that and it returns the following unhelpful error"Incorrect syntax near '@Trans_type'"
Munklefish
+2  A: 

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

TLiebe
I just tried that and it returns the following unhelpful error"Incorrect syntax near '@Trans_type'"
Munklefish
+1  A: 

yeah Nick is right.

You need to use SET or SELECT to assign to @tmpType

John Nolan
I just tried that and it returns the following unhelpful error"Incorrect syntax near '@Trans_type'"
Munklefish
See my answer for the next fix...
RichardOD
+3  A: 

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!

RichardOD
Excellent thanks. Silly mistake to make. ;-(
Munklefish
Glad it is sorted now.
RichardOD
A: 

try

IF(@Trans_type = 'subscr_signup')    
BEGIN 
 set @tmpType = 'premium' 
 END
ELSE iF(@Trans_type = 'subscr_cancel')  
  begin
     set    @tmpType = 'basic'  
  END
Pragnesh Patel
That won't work. Else needs to be else if.
RichardOD
bit of an odd comment coming so long after the real answer has been revealed. ;-)
Munklefish
A: 

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'
John