SQL Server Performance

Sum function result

Discussion in 'General DBA Questions' started by Leite33, Dec 17, 2006.

Page 1 of 2

  1. Leite33 New Member

    Hi
    I made the above... My fuction works good because its says a message everything ok
    But i dont get the relust table why???



    USE Test
    GO
    CREATE FUNCTION compare5 (@FieldA Int,@FieldB Int)
    RETURNS Table
    AS
    Return
    (
    SELECT FieldA+' '+FieldB As Result
    From Fields
    )

    Leite33, Dec 17, 2006
    #2

  2. FrankKalis Moderator

    Probably because you're not using the @FieldA and @FieldB parameter anywhere in the function?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Dec 18, 2006
    #2

  3. Leite33 New Member

    What you mean??? Ofcourse i use them in Select
    Leite33, Dec 18, 2006
    #2

  4. FrankKalis Moderator

    Then is the SELECT you've showed not the SELECT you use?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Dec 18, 2006
    #2

  5. Leite33 New Member

    Oh god!!! I still dont get it where is the wrong
    Can you correct the code i have wrong?????
    Leite33, Dec 18, 2006
    #2

  6. madhuottapalam New Member

    SELECT @FieldA+' '+ @FieldB As Result From Fields

    madhu
    madhuottapalam, Dec 18, 2006
    #2

  7. Adriaan New Member

    Leithe33 - what are you trying to do? Your UDF definition is trying to concatenate two integer values with a space in between.

    As a wonderful but weird side-effect of T-SQL, the result is indeed the sum of the two integer values. But I wouldn't recommend using this syntax anyway.

    If you want to show the two integers with a space in between, use CAST or CONVERT on both integers - and never use a UDF for basic stuff that SELECT can handle all by itself:

    SELECT CAST(int1 AS VARCHAR) + ' ' + CAST(int2 AS VARCHAR)
    FROM table

    Note that the result of this is no longer a numeric value.
    Adriaan, Dec 18, 2006
    #2

  8. Leite33 New Member

    I have done these and my code is ok!!! But how i will see the result!????? Call the function i create but how???? With Selecttttttt
    Leite33, Dec 18, 2006
    #2

  9. FrankKalis Moderator

    Probably we should start differently. Can you post the table structure of "Fields", some sample data and what your desired resultset should look like? I mean what are you trying to compare?

    From your naarative I would have guessed you SELECT statement looks something like:


    SELECT FieldA+' '+FieldB As Result
    FROM Fields
    WHERE FieldID (?!?) IN (@Field, @FieldB)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Dec 18, 2006
    #2

  10. Adriaan New Member

    Don't use a function for such basic functionality. Functions are usually bad for performance, so do not use them unless there is no other option.

    Also, you are not clear on whether you want to SUM (calculation) or CONCATENATE (string together) the two values.

    Your table has two columns, A and B. The value on column A is 1, on B the value is 2.

    Calculation:
    SELECT A + B FROM YourTable
    >>> Result: 3

    Concatenation:
    SELECT CAST(A AS VARCHAR) + ' ' + CAST(B AS VARCHAR) FROM YourTable
    >>> Result: 1 2


    ***

    Bad syntax:
    SELECT A + ' ' + B FROM YourTable
    >>> Result: 3

    In my opinion, this query should give you either an error message or the "1 2" result - but SQL Server is forgiving.
    Adriaan, Dec 18, 2006
    #2

  11. Leite33 New Member

    Ok
    i have a table 'Fields'
    with 2 fields FieldA and FieldB

    i want to create a function(not something else but a functionnnnnnn)
    that will take 2 arguments FieldA and FieldB and bring the result to a table
    FieldA FieldB Result
    Leite33, Dec 18, 2006
    #2

  12. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br />SELECT FieldA, FieldB, FieldA + FieldB as [Result]<br /> FROM Fields<br /></font id="code"></pre id="code"><br />That's all you need. There is really no need for a function. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 18, 2006
    #2

  13. Leite33 New Member

    FrankKalis you break my nervous!!! I know that way!!! I want to know how function workssssssssssssss!!!!!!!!!
    Let me ask something else:

    if you have:
    Table: Values

    Fields:
    Ao A1
    2 8
    3 10
    4 30
    5 40

    And want to calculate the last fields (5,40)
    (5- each of other fields)^2
    +
    (40-each of other fields)^2

    for example:
    Result=(5-4)^2+(40-30)^2
    Result2=(5-3)^2+(40-10)^2

    How you will do it??? The fields are many about 100 (Ao,A1,A2...A100) and the values are 10 of each field.

    Let me see now if you are good
    Leite33, Dec 18, 2006
    #2

  14. Adriaan New Member

    Leite33,

    Use derived tables ...

    SELECT (A.Ao - B.Ao) ^2 + (A.A1 - B.A1)^2
    FROM
    (SELECT Ao, A1 FROM Values WHERE Ao = (SELECT MAX(Ao) FROM Values)) AS A,
    (SELECT Ao, A1 FROM Values WHERE Ao < (SELECT MAX(Ao) FROM Values)) AS B

    The one with alias A selects the row with the maximum Ao value, the one with alias B selects all rows with a lesser Ao value. The expression after SELECT does the calculation on all combinations of values from A and B, as there is no JOIN or WHERE clause.
    Adriaan, Dec 18, 2006
    #2

  15. Adriaan New Member

    I might add that if you have to solve this for 100 columns, then the data is perhaps not properly normalized.
    Adriaan, Dec 18, 2006
    #2

  16. Adriaan New Member

    Plus - in my defense - I have little or no understanding of statistics. Please check Books Online for aggregate queries which might help you solve puzzles of the statistical kind.
    Adriaan, Dec 18, 2006
    #2

  17. danny123 New Member


    declare @C1 int
    Declare @C2 int
    Declare @C3 varchar(255)
    declare crsr_tbl cursor for (select top 4 c1,c2 from tstTbl_02 )
    open crsr_tbl
    Fetch next from crsr_tbl into @C1,@C2
    while @@fetch_status = 0
    begin
    Set @c3 = (5-@c1)^2 + (50-@c2)^2
    Print 'Result @c1' + @c3
    fetch next from crsr_tbl into @c1,@c2
    end
    close crsr_Tbl
    deallocate crsr_Tbl
    go
    danny123, Dec 18, 2006
    #2

  18. Leite33 New Member

    Ok Danny thanks... But can you do it if you dont know the values...
    For example......

    Use last Record Fields and make the same with rest fields of Records
    Leite33, Dec 18, 2006
    #2

  19. Adriaan New Member

    Have you seen my last post on the previous page, about using derived tables?

    sorry for the typos
    Adriaan, Dec 18, 2006
    #2

  20. Madhivanan Moderator

    quote:Originally posted by Leite33

    FrankKalis you break my nervous!!! I know that way!!! I want to know how function workssssssssssssss!!!!!!!!!
    Let me ask something else:

    if you have:
    Table: Values

    Fields:
    Ao A1
    2 8
    3 10
    4 30
    5 40

    And want to calculate the last fields (5,40)
    (5- each of other fields)^2
    +
    (40-each of other fields)^2

    for example:
    Result=(5-4)^2+(40-30)^2
    Result2=(5-3)^2+(40-10)^2

    How you will do it??? The fields are many about 100 (Ao,A1,A2...A100) and the values are 10 of each field.

    Let me see now if you are good
    1 This is entirely different from your function code
    2 Where do you want to show data?
    3 I dont find any reason on why you want to do this in Function
    4 Try Adriaan's suggestion

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Dec 18, 2006
    #2

  21. danny123 New Member

    May be you van use something like below to get the values in the last row if you have specific order in any of the column:

    select top 1 c1,c2
    from <table Name>
    order by <column name > asc |desc

    see if this works.

    Thanks
    Danny
    danny123, Dec 18, 2006
    #2

  22. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Leite33</i><br /><br />FrankKalis you break my nervous!!! I know that way!!! I want to know how function workssssssssssssss!!!!!!!!!<br />Let me ask something else:<br /><br />if you have:<br />Table: Values<br /><br />Fields:<br />Ao A1 <br />2 8<br />3 10<br />4 30<br />5 40<br /><br />And want to calculate the last fields (5,40)<br />(5- each of other fields)^2 <br />+<br />(40-each of other fields)^2 <br /><br />for example:<br />Result=(5-4)^2+(40-30)^2 <br />Result2=(5-3)^2+(40-10)^2<br /><br />How you will do it??? The fields are many about 100 (Ao,A1,A2...A100) and the values are 10 of each field.<br /><br />Let me see now if you are good<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You know, one of the major problems in human communication is the lack of precision. What you tell us now is completely different from what you've originally asked in this thread and the other 2 you've started. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />You could have us all saved us time and efforts, if you had stated clearly what you are after. Without digging much deeper into this requirement, I would go for a cursor, if it needs to be done at the server. But such row positioning problems are more efficiently soved at the client.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 18, 2006
    #2

  23. danny123 New Member

    Frank - Can you please suggest me some good article on cursors....as you just specified something like server side and client side....and i dont know much about it.
    Please correct the solution i sugested and let me know the best possible solution.

    Thanks
    Danny
    danny123, Dec 18, 2006
    #2

  24. Madhivanan Moderator

    quote:Originally posted by danny123

    Frank - Can you please suggest me some good article on cursors....as you just specified something like server side and client side....and i dont know much about it.
    Please correct the solution i sugested and let me know the best possible solution.

    Thanks
    Danny
    1 When you say server side and client side cursor it is about Cursor type of the recordset you use in the front end application.
    2 In sql, you can do almost everything without using a cursor
    3 Dont try to apply front end's while and for loops in sql
    4 You post this as new topic to get more replies

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Dec 18, 2006
    #2

  25. FrankKalis Moderator

    quote:Originally posted by danny123

    Frank - Can you please suggest me some good article on cursors....as you just specified something like server side and client side....and i dont know much about it.
    Please correct the solution i sugested and let me know the best possible solution.

    Thanks
    Danny
    You're solution looks okay. With the exception that you've hardcoded the 5 and the 40. You would want to keep that flexible.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Dec 18, 2006
    #2
Page 1 of 2

Share This Page