[ACCEPTED]-IF/ELSE Stored Procedure-stored-procedures

Accepted answer

Score: 16

Nick is right. The next error is the else 10 should be else if (you currently have a 9 boolean expression in your else which makes 8 no sense). Here is what it should be

ELSE IF(@Trans_type = 'subscr_cancel')
    BEGIN
    SET @tmpType = 'basic'
    END

You 7 currently have the following (which is wrong):

ELSE(@Trans_type = 'subscr_cancel')
    BEGIN
    SET @tmpType = 'basic'
    END

Here's 6 a tip for the future- double click on the 5 error and SQL Server management Studio will 4 go to the line where the error resides. If 3 you think SQL Server gives cryptic errors 2 (which I don't think it does), then you 1 haven't worked with Oracle!

Score: 8

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

Score: 3

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

Score: 1

try

set @tmptype

Score: 1

yeah Nick is right.

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

Score: 0

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

ie:

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

Score: 0

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

Source: stackoverflow.com

MS SQL Server: Check to see if a user can execute a stored procedure

Eliminate and reduce overlapping date ranges

Stored Procedures to .sql files

Select Average of Top 25% of Values in SQL

How do I pass input parameters to sp_executesql?

Inserting rows into a table with one IDENTITY column only

Sorting empty values last instead of first

Get the IP address of a user in calling a stored procedure (SQL)

How to find which columns don't have any data (all values are NULL)?

Conditional Operator in SQL Where Clause

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.