Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Use IF instead of CASE:
    Code:
    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
    ...

  3. #3
    Join Date
    Apr 2006
    Posts
    16

    Can't pass in parameters to the inner Stored Procedure

    Thanks for the response, the if--else--if works.
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •