Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: How do i specify more than i argument in a called SP?

    CREATE PROCEDURE sp_getT
    @m1 int ,
    @txn int ,
    @Pan varchar(50) ,
    @Act varchar(50) OUTPUT,
    @Bal Decimal(19,4) OUTPUT,
    @CBal Decimal(19,4) OUTPUT
    AS

    declare @pBal money, @pCbal money, @pAct money
    SET NOCOUNT ON


    IF @m1 = 200
    BEGIN
    IF @txn = 31
    BEGIN
    exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
    END
    END

    SET @Act = @pAct
    SET @Bal = cast(@pBal as Decimal(19,4))
    SET @CBal = cast(@pCBal as Decimal(19,4))

    return @Act
    return @Bal
    return @CBal

    the above code returns this error message

    "Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
    Procedure or function ChkBal has too many arguments specified."


    How do i specify all the arguments i want in the called procedure?

  2. #2
    Join Date
    Jan 2007
    Posts
    6
    As the message describes, this error is happens when you are passing or arguments to a stored procedure and the number of arguments doesn not match the number your stored procedure expects.

    Can you post the ChkBal stored procedure so I can see what you are trying to call.

  3. #3
    Join Date
    Apr 2006
    Posts
    16
    Thanks Dwane.

    Here is the 2nd sp:



    ALTER PROCEDURE [dbo].[ChkBal] @Pan varchar(50), @pAct varchar(50) OUTPUT, @pBal money OUTPUT, @pCBal money OUTPUT
    as
    declare @act varchar(30),@bal money, @cBal money

    declare @wkStat int


    set @wkStat = 0
    declare acct_c cursor for select acctno, bal, cbal from accts
    where acctno = @pan
    open acct_c
    fetch next from acct_c into @act, @bal, @cBal

    if @@FETCH_STATUS <> 0
    begin
    set @wkstat =1
    goto error_out
    end

    else
    set @pAct = @act
    set @pBal = @bal
    set @pCBal = @cBal

    close acct_c
    deallocate acct_c

    return @pAct
    return @pBal
    return @pCBal

    error_out:
    close acct_c
    deallocate acct_c


    return @wkstat

    GO
    Last edited by BYemi; 01-24-07 at 07:22. Reason: there is a mistake in the code

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by BYemi
    exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
    Try writing the last word in this statement as OUTPUT instead of OUT. It's probably an interpretation error due to this mistake.

  5. #5
    Join Date
    Apr 2006
    Posts
    16
    hi,
    av done it and it's still not working.

  6. #6
    Join Date
    Apr 2006
    Posts
    16
    Lexiflex, am i missing something or is it that a called stored procedure can only take in 1 argument?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A stored procedure can have only one return value, but can have up to 250 or so output parameters. You do not have to use the return keyword for output parameters.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Here is an example of the difference:
    Code:
    create procedure test1 (@out1 int output, @out2 varchar(10) output, @out3 datetime output)
    as
    select @out1 = 10, @out2 = 'hello', @out3 = getdate()
    return 55
    
    go
    
    declare @return int
    declare @var1 int
    declare @var2 varchar(10)
    declare @var3 datetime
    
    exec @return = test1 @var1 output, @var2 output, @var3 output
    
    select @return, @var1, @var2, @var3

  9. #9
    Join Date
    Apr 2006
    Posts
    16
    Hi MCrowley,
    av done all that but now am receiving this error message :

    Server: Msg 257, Level 16, State 2, Procedure ChkBal, Line 0
    Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.



    Stored Procedure: CUCB.dbo.sp_getT
    Return Code = 0
    Output Parameter(s):
    @Act = <NULL>
    @Bal = <NULL>
    @CBal = <NULL>


    it's so frustrating!

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Judging from the cursor, you must be coming from an Oracle background. If you remove the cursor, this will go faster. As for the data conversion error, check the accts table to see what the bal and cbal columns are defined as.

  11. #11
    Join Date
    Apr 2006
    Posts
    16
    Thanks once again, you are right abt my coming from an Oracle bkground. Now the Bal and cBal are both 'money' datatype. Also, what do i replace with the cursor to make the query perform better?

  12. #12
    Join Date
    Apr 2006
    Posts
    16
    MCrowley, thank u. av solved it

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This would be the simplified version:
    Code:
    ALTER PROCEDURE [dbo].[ChkBal] @Pan varchar(50), @pAct varchar(50) OUTPUT, @pBal money OUTPUT, @pCBal money OUTPUT
    as
    
    declare @wkStat int
    
    set @wkStat = 0
    
    select @pAct = acctno, @pBal= bal, @pCBal = cbal 
    from accts
    where acctno = @pan
    
    if @@error <> 0
      begin
    	set @wkstat =1
      end
    
    return @wkstat
    
    GO
    I think the conversion error must be coming from the call to the inner stored procedure. Parameters are passed in order, unless otherwise specified, so you want to have either:
    Code:
    exec ChkBal @Pan, @pAct out , @pBal output, @pCbal output
    or
    Code:
    exec ChkBal @Pan = @Pan, @pBal = @pBal output, @pCbal = @pCbal output, @pAct = @pAct out
    It looks more confusing than it is, because you have the same parameter and variable names, which is not a bad practice. In this case, I would probably just pass them in order, as the first example.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by MCrowley
    In this case, I would probably just pass them in order, as the first example.
    IMO passing unnamed params is fragile.

    if someone adds a parameter in the middle of the list in the sproc definition, the code calling the sproc with unnamed params is broken. Or, if a parameter is removed, the call may continue to "work" but produce unexpected results.

    I always use named params in sproc calls for this reason. also it's more readable - you don't have to remember which param is which if they are named.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Excellent point, Jezemine.

Posting Permissions

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