• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1502
  • Last Modified:

How Stored Procedure write multiple IF..ELSE block?

Hi

I need to write multiple IF ELSE conditions in the following Stored Procedure.

But, it shows Incorrect syntax near the keyword 'ELSE'.

What's wrong?
BEGIN TRAN Tran1
DECLARE @status int
DECLARE @number int
 
EXEC @status = SP_GetStatus @userid
IF @status = 1 --IF One
 
EXEC @number = SP_GetNumber @userid
 
	IF @number > 0 --IF Two
 
		SELECT * from user where id=@userid and status=@status
 
	ELSE --ELSE Two number < 0
 
ELSE --ELSE One user status = 0
 
    return 0
 
COMMIT TRAN Tran1

Open in new window

0
techques
Asked:
techques
  • 10
  • 7
  • 2
  • +2
3 Solutions
 
cuziyqCommented:
You need to use BEGIN/END blocks on your IF statements.  The SQL command parser is not smart enough to infer it from your script.
0
 
cuziyqCommented:
Oh yeah . . . also, the line

ELSE --Else two number < 0 . . .

Should it read ELSE number < 0 --ELSE Two ?

The -- indicates that the rest of the line is a comment, so your inner IF statement's ELSE clause has no body.
0
 
SharathData EngineerCommented:

BEGIN TRAN Tran1
DECLARE @status int
DECLARE @number int
 
declare @userid int 
EXEC @status = SP_GetStatus @userid
IF @status = 1 --IF One
 
EXEC @number = SP_GetNumber @userid
 
	IF @number > 0 --IF Two
 
		SELECT * from #user where id=@userid and status=@status
 
	ELSE IF @number < 0 -- Two number < 0
print 'do something'  -- include a statement here
ELSE --ELSE One user status = 0
 print 'do something' 
    return 0
 
COMMIT TRAN Tran1

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
techquesAuthor Commented:
Hi

1) I added Being and End if every IF ELSE Block, still same error
2) -- does not affect the SP code as    -- Description:      <Description,,> is also on the top
3) I added print 'do something' after every ELSE, still same error (and it cannot use Else If in my logic, they must be combined with several IF...Else nested block)

How should I solve?
0
 
SharathData EngineerCommented:
don't use return in sp. check this.
BEGIN TRAN Tran1
DECLARE @status int
DECLARE @number int
 
declare @userid int 
EXEC @status = SP_GetStatus @userid
IF @status = 1 --IF One
 
EXEC @number = SP_GetNumber @userid
 
	IF @number > 0 --IF Two
 
		SELECT * from #user where id=@userid and status=@status
 
	ELSE IF @number < 0 -- Two number < 0
print 'do something'  -- include a statement here
ELSE --ELSE One user status = 0
 print 'do something' 
    --return 0
 
COMMIT TRAN Tran1

Open in new window

0
 
techquesAuthor Commented:
Hi

I removed all return after ELSE, but still
Incorrect syntax near the keyword 'ELSE'.

Also, it has to return certain number for each block of IF ELSE, as it needs to handle different cases in VC# for different return values.
0
 
SharathData EngineerCommented:
post your entire sp.
0
 
techquesAuthor Commented:
ALTER PROCEDURE [dbo].[SP_Insert](
@price Decimal(18,2),
@userid int,
@productid int
)
AS
BEGIN TRAN Tran_1
DECLARE @result int
DECLARE @statusid int
DECLARE @number int
DECLARE @isoldprice int

EXEC @statusid = SP_GetStatus @userid

IF @statusid = 1

EXEC @number = SP_GetNumber @userid

      IF @number > 0

EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
       
            IF @isoldprice = 0

                      SELECT * from user where id=@userid and status=@statusid

            ELSE
                print 'do something'

      ELSE
        print 'do something'

ELSE
    print 'do something'

COMMIT TRAN Tran_1
0
 
SharathData EngineerCommented:
you have nultiple ELSE statements, you need t include a condition for each ELSE. This is not mandatory for last ELSE.
ALTER PROCEDURE [dbo].[SP_Insert]( 
@price Decimal(18,2),
@userid int,
@productid int
)
AS
BEGIN TRAN Tran_1
DECLARE @result int
DECLARE @statusid int
DECLARE @number int
DECLARE @isoldprice int
 
EXEC @statusid = SP_GetStatus @userid
 
IF @statusid = 1 
 
EXEC @number = SP_GetNumber @userid
 
      IF @number > 0 
 
EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
        
            IF @isoldprice = 0 
 
                      SELECT * from user1 where id=@userid and status=@statusid
 
            ELSE IF @isoldprice < 0 --include a condition here
                print 'do something' 
 
      ELSE IF @isoldprice > 0 -- another condition here
        print 'do something' 
 
ELSE 
    print 'do something' 
 
COMMIT TRAN Tran_1

Open in new window

0
 
wickedlysmartCommented:
you can not use an ELSE without any statement to perform. if you are not doing anything in your nested ELSE branch then remove it. the following line is to be removed from your code

 ELSE --ELSE Two number < 0
0
 
techquesAuthor Commented:
Hi


IF @statusid = 1 
 
EXEC @number = SP_GetNumber @userid
 
      IF @number > 0 
 
EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
        
            IF @isoldprice = 0 
 
                      SELECT * from user1 where id=@userid and status=@statusid
 
            ELSE IF @isoldprice < 0 --This condition is for @isoldprice = 0
                print 'do something' 
 
      ELSE IF @isoldprice > 0 -- However, this condition is not for @isoldprice = 0, it should for @number > 0
        print 'do something' 
 
ELSE --This condition is for @statusid = 1
    print 'do something'     
    
So, the IF ELSE block structure should be
IF 1
IF 2
IF 3
ELSE 3
ELSE 2
ELSE 1
 
But not
IF 1
IF 2
IF 3
ELSE IF 3
ELSE IF 3
ELSE --not know which If refer to
 
Also, but not
IF 1
ELSE 1
IF 2
ELSE 2
IF 3
ELSE 3
 
And, how can it return a number in different ELSE condition?

Open in new window

0
 
techquesAuthor Commented:
Even I use
ELSE IF
ELSE IF
ELSE
still get the same error.

How can I write the nested IF ELSE block in SP?
0
 
reb73Commented:
Use clear indenting to guide you in matching else statements with the if block..

See code below -
ALTER PROCEDURE [dbo].[SP_Insert]
( 
	@price Decimal(18,2),
	@userid int,
	@productid int
)
AS
BEGIN TRAN Tran_1
DECLARE @result int
DECLARE @statusid int
DECLARE @number int
DECLARE @isoldprice int
 
EXEC @statusid = SP_GetStatus @userid
 
IF @statusid = 1 
BEGIN 
	EXEC @number = SP_GetNumber @userid
 
	IF @number > 0 
 	BEGIN		-- Multiple statements, use begin-end
		EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
        
            	IF @isoldprice = 0 	-- Single statement, no begin-end
			SELECT * FROM user1 WHERE ID=@userid and status=@statusid
		ELSE IF @isoldprice < 0 --include a condition here
	                PRINT 'do something'  
		ELSE IF @isoldprice > 0 -- another condition here
        		PRINT 'do something' 
 	END
	ELSE 
	    PRINT 'Number not greater than 0' 
END
ELSE
	PRINT 'Status Id does not equal one'
 
COMMIT TRAN Tran_1
GO

Open in new window

0
 
wickedlysmartCommented:
your branching is not appropriate. IF statement affects only the succeeding statement if you dont use BEGIN...END block. your code is considering all the ELSE branches for the last IF statement. but it seems you have ELSE branches for each of your IF statement. the following code should solve your problem


ALTER PROCEDURE [dbo].[SP_Insert]( 
@price Decimal(18,2),
@userid int,
@productid int
)
AS
BEGIN TRAN Tran_1
DECLARE @result int
DECLARE @statusid int
DECLARE @number int
DECLARE @isoldprice int
 
--EXEC @statusid = SP_GetStatus @userid
 
IF @statusid = 1 
BEGIN
 
EXEC @number = SP_GetNumber @userid
 
      IF @number > 0 
      BEGIN
 
EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
        
            IF @isoldprice = 0 
 
                      SELECT * from user where id=@userid and status=@statusid                      
 
            ELSE 
                print 'do something' 
		END
      ELSE 
        print 'do something' 
END
ELSE 
    print 'do something' 
 
COMMIT TRAN Tran_1

Open in new window

0
 
SharathData EngineerCommented:
techques - did you check my post (ID: 23709060). Please give some condition to other ELSE IF statements.
0
 
techquesAuthor Commented:
Hi

After adding Begin and end for each if else block, it can be executed.

However, I do not want to PRINT 'do something'
Instead of that, I need to return an integer, e.g. 1 for first IF ELSE block, and 2 for 2nd IF ELSE block....

How should I write it?

Since there are many experts, I increase the points.

Thanks for all of your help
0
 
SharathData EngineerCommented:
Let me clear you one thing that SP is nothing but a set of one or more statements. If you want to return a value, use function. Create function like this and call this function in your code as below.
SELECT dbo.fn_MyFunction(paramters)
hope this is clear.

create function [dbo].[fn_Insert]( 
@price Decimal(18,2),
@userid int,
@productid int
) returns int 
AS
begin
DECLARE @result int
DECLARE @statusid int
DECLARE @number int
DECLARE @isoldprice int
 
--EXEC @statusid = SP_GetStatus @userid
 
IF @statusid = 1 
BEGIN
 
EXEC @number = SP_GetNumber @userid
 
      IF @number > 0 
      BEGIN
 
EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
        
            IF @isoldprice = 0 
 
                      return 1 --SELECT * from user1 where id=@userid and status=@statusid                      
 
            ELSE if @isoldprice < 0  -- provide some condtion here 
                return 2
		
      ELSE  if  @isoldprice > 0 -- Provide some condition here
        return 3
 
ELSE 
    return 4  --print 'do something' 
 
end
end
return 5
end
 
 

Open in new window

0
 
techquesAuthor Commented:
Dear Sir

There are 3 functions when I browse NEW functions:
1. Inline Table-valued
2. Multi-statements Table-valued
3. Scalar-valued

Which one is suitable as the code does a lot of select, calculation, update, insert queries.

And, how to call that function? Is it the same way to call a SP?
like your example: SELECT dbo.fn_MyFunction(paramters)
and it will return the value?
0
 
techquesAuthor Commented:
Moreover, can Function do the transaction SQL: commit and rollback?

 
0
 
reb73Commented:
Functions cannot call a stored procedure (only certain extended stored procedure calls are allowed) and cannot perform UPDATE/DELETE actions on persistent tables/views in the database..

If you need a return value, create an output parameter to the stored procedure similar to the code below -
ALTER PROCEDURE [dbo].[SP_Insert]
	 @price Decimal(18,2)
	,@userid int
	,@productid int
	,@returnvalue int output
AS
BEGIN
	DECLARE @result int
	DECLARE @statusid int
	DECLARE @number int
	DECLARE @isoldprice int
	 
	--EXEC @statusid = SP_GetStatus @userid
	BEGIN TRAN Tran_1
	IF @statusid = 1 
	BEGIN
		EXEC @number = SP_GetNumber @userid
		IF @number > 0 
		BEGIN
			EXEC @isoldprice = SP_isOldPrice @price, @productid, @userid
			IF	(@isoldprice = 0)
			BEGIN
			      SELECT * from [user] where id=@userid and status=@statusid                      
			      SET @returnvalue = 1	
			END
			ELSE 
			      SET @returnvalue = 2
		END
	        ELSE 
	        	SET @returnvalue = 3
	END
	ELSE 
		SET @returnvalue = 4
	COMMIT TRAN Tran_1
END
 
/* 
	call the stored procedure as follows (sample values, update as necessary)
	DECLARE @retval int
	EXEC [dbo].[SP_Insert] @price = 100.05, @userid = 1, @productid = 1101, @retval output
	SELECT @retval
*/

Open in new window

0
 
SharathData EngineerCommented:
Yes, you need proc in this situation with output parameter.
0
 
techquesAuthor Commented:
Thank you so much. I add more points.
0
 
techquesAuthor Commented:
Fast response, good illustration with code example, with how to call SP example. Anyway, to me, it is an excellent solution and I learnt a lot from the experts.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now