Friday, February 24, 2012

Error while trying to execute an SP in my CASE STATEMENT

Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.

Here is my code:

Alter PROCEDURE sp_getTxn (
@.m1 int = Null,
@.txn int = Null,
@.p2 int = Null,
@.amt int = Null,
@.pAccountno varchar(50) = 'Null',
@.DAcct int = Null,
@.Balance Decimal(19,4) = NULL OUTPUT,
@.pBalance Decimal(19,4) = NULL OUTPUT,
@.RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

select CASE
WHEN @.m1 = 200 THEN case
when @.txn = 00 then ('exec dbo.CustOrderHist (@.CrAcct int)')

when @.txn = 01 then ('exec dbo.Sp_withdrawal')

when @.txn = 31 then exec dbo.CheckBalance(@.pAccountno varchar(50), @.pBalance Decimal(19,4) OUTPUT)

when @.txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @.m1 = 420 THEN case
when @.txnType = 00 then ('exec dbo.Sp_reversal')

when @.txnType = 01 then ('exec dbo.Sp_reversal2')

when @.txnType = 31 then ('exec dbo.Sp_reversal3')
END
END

SET @.Balance = @.pBalance
Print @.Balance

Or is there an alternative to the above CASE statement that is easier and faster?

ThanksUse IF instead of CASE:
IF @.m1 = 200
BEGIN
IF @.txn = 00
BEGIN
exec dbo.CustOrderHist (@.CrAcct int)
END
ELSE IF @.txn = 01
BEGIN
exec dbo.Sp_withdrawal
END
ELSE IF @.txn = 31
...
END
ELSE IF @.m1 = 420
BEGIN
...|||Thanks for the response, the if--else--if works.
...

No comments:

Post a Comment