SQL IF/ELSE 存储过程

IF/ELSE Stored Procedure

提问by Munklefish

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

回答by RichardOD

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 应该是 else if （您目前在 else 中有一个布尔表达式，这是没有意义的）。这是应该的

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!

这是未来的提示 - 双击错误，SQL Server management Studio 将转到错误所在的行。如果您认为 SQL Server 给出了神秘的错误（我认为不会），那么您就没有使用过 Oracle！

回答by Nick

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

它没有给出任何错误？尝试
SET @tmpType = 'premium'
和
SET @tmpType = 'basic'

回答by TLiebe

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

在 IF .. ELSE 块中分配变量时是否缺少“SET”语句？

回答by Johnno Nolan

yeah Nick is right.

是的，尼克是对的。

You need to use SETor SELECTto assign to @tmpType

您需要使用SET或SELECT分配给@tmpType

回答by HLGEM

try

尝试

set @tmptype

回答by John

Just a tip for this, you don't need the BEGIN and END if it only contains a single statement.

只是一个提示，如果它只包含一个语句，则不需要 BEGIN 和 END。

ie:

IE：

IF(@Trans_type = 'subscr_signup')    
 set @tmpType = 'premium' 
ELSE iF(@Trans_type = 'subscr_cancel')  
     set    @tmpType = 'basic'

回答by Dinesh Vaitage

Try this with SQL join statements

用 SQL 连接语句试试这个

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

回答by Pragnesh Patel

try

尝试

IF(@Trans_type = 'subscr_signup')    
BEGIN 
 set @tmpType = 'premium' 
 END
ELSE iF(@Trans_type = 'subscr_cancel')  
  begin
     set    @tmpType = 'basic'  
  END