Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: can this function work?

    hil

    I have the following function,

    CREATE FUNCTION ABC ( @ipo BIGINT, @ipport INT )
    RETURNS INT
    AS
    BEGIN
    DECLARE @PT BIGINT
    select @PT = @ipport + '123' from IPTABLE where IPO = @ipo;
    RETURN @PT;
    END;


    I want to know if it can achieve this goal

    to pass in the IPO and return the PT number

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Do not quite understand the purpose of his function, but I think that '123' should not be quoted, and the return should also be of type BigInt.

    Hope this helps.

  3. #3
    Join Date
    Jul 2012
    Posts
    10
    I just made it up '123'. Beside that, what else do you think I should do and if this function can achieve this goal?

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    As this is completely new (i suspect), suggest you try it on your test environment and tweak it as needed.

    If there are any surprises in the testing, post what you tried and what happened. Someone may have a suggestion.

  5. #5
    Join Date
    Jul 2012
    Posts
    10
    I dump it in SSMS, it create the function, but how to test it from input, then see the output?

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    CREATE FUNCTION ABC ( @ipo BIGINT, @ipport INT )
    RETURNS INT
    AS
    BEGIN
    DECLARE @PT BIGINT
    --if you are defining this as a BIGINT, why is the RETURNS of the function an INT ???
    select @PT = @ipport + '123' from IPTABLE where IPO = @ipo;
    --you have defined @ipport as an INT, but here you are "adding" it to a string--those quotes around the 123 explicitly state that that is a string. So, are your trying to add two numbers, or trying to catenate strings?
    --we have no idea if IPO = @ipo is unique in the table IPTABLE.
    RETURN @PT;
    END;

    Unless you are, by design, trying to make this a complex as possible, why do you need to do this in a function???

    This looks like you should be using a simple JOIN clause in the query that would be calling this function.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Jul 2012
    Posts
    10
    if not by function, how do you do by a simple join?

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Show us the query you are using that contains the call to this function.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Jul 2012
    Posts
    10
    This is query I use

    select ipport + '123' as num from IPTABLE where IPO = @ipo;

    Also, how to test it in SSMS?

    I use

    EXEC ABC @ipo???

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You wrote a function so that you could write a query something like this:

    select dbo.ABC(ST.Field12,ST.Field34) from SOME_TABLE as ST

    What is your query that uses (calls) the function ABC ???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If this worked as you want, what process would it support?

    Possibly there is something not yet explained . . .

Posting Permissions

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