Post reply

Question on SP

  • September 11, 2009 at 9:16 am

    #100671

    I have a function created as GetEvDate,

    then call SP test

    exec test 'xxxx', GetEvdate('xxx')

    but got error, how to fix this? thanks a lot

  • September 11, 2009 at 9:33 am

    #1052383

    What does the stored procedure return?

  • September 11, 2009 at 9:36 am

    #1052385

    the SP doesn't return value, i try to create a table inside the SP and insert value, the function return a date, and i want to pass this to the SP.

  • September 11, 2009 at 9:42 am

    #1052389

    Sorry, tired eyes. I read your post wrong. First, it would help to see the actual error message. Second, it would also help if you would post your code.

  • September 11, 2009 at 9:55 am

    #1052402

    Incorrect syntax near '.'

    when i do select dbo.GetEvdate ('xxxxx'), it return value correctly.

    but when i do exec test '1111', dbo.GetEvdate ('xxxxx'), get syntax error.

    thanks

  • September 11, 2009 at 10:13 am

    #1052420

    From BOL: "Parameter values can be supplied if a stored procedure is written to accept them. The supplied value must be a constant or a variable. You cannot specify a function name as a parameter value. Variables can be user-defined or system variables, such as @@SPID."

    You will have to get the output of the function in a variable and then pass it to the SP.

    HTH,

    Sups

  • September 11, 2009 at 9:53 pm

    #1052653

    shuzi (9/11/2009)

    I have a function created as GetEvDate,

    then call SP test

    exec test 'xxxx', GetEvdate('xxx')

    but got error, how to fix this? thanks a lot

    The problem with your code is that you cannot use any form of calculation in an EXEC parameter... functions included. Assign a variable to the value the function returns and use the variable in the EXEC.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 11, 2009 at 9:56 pm

    #1052654

    Heh... well THAT's interesting. Sup's post didn't appear when I replied. Sorry for any confusion folks. Guess I'm just confirming what Sup posted.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 14, 2009 at 8:01 am

    #1053219

    thanks all

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply