[ACCEPTED]-IF/ELSE Stored Procedure-stored-procedures
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!
It isn't giving any errors?
SET @tmpType = 'premium'
SET @tmpType = 'basic'
Are you missing the 'SET' statement when 2 assigning to your variables in the IF .. ELSE 1 block?
yeah Nick is right.
You need to use
SELECT to 1 assign to @tmpType
Just a tip for this, you don't need the 2 BEGIN and END if it only contains a single 1 statement.
IF(@Trans_type = 'subscr_signup') set @tmpType = 'premium' ELSE iF(@Trans_type = 'subscr_cancel') set @tmpType = 'basic'
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
More Related questions