Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    Unanswered: Storeprocedure Select Query Error

    Hi toevery one
    I have a Big problem while Selecting the Recordset. I Need to capture that Error. Using this error i have to do some operation.
    my Table has the records like

    ItmCode (Datatype varchar(20))
    -------
    101
    102
    103B
    104

    Select Cost(ItmCode as Int) from MyTable
    It is giving Error that is correct because one Code has Character (103B)

    Server: Msg 245, Level 16, State 1, Procedure spGLGloRefMax, Line 17
    Syntax error converting the varchar value '11A' to a column of data type int.
    So I need to find out the Error Like
    If error=somethig then
    my procedure
    else
    soem rotuine
    endif

    Like that Please Help me.
    If some one solve this problem He is the man
    -- Prasad --

  2. #2
    Join Date
    Jul 2002
    Location
    Bucharest - Romania
    Posts
    6
    try this:

    select itmCode, case isnumeric(itmCode) when 1 then 'isnumeric'
    else 'isnotnumeric'
    end
    from MyTable

  3. #3
    Join Date
    Feb 2002
    Location
    India
    Posts
    38
    When i am slelecting the rows using max(ItmCode) It is displaying
    wrong result.
    My recods set is
    Code:
    Itemcode 
    -------------------------
    1
    6
    6
    7
    7
    9
    10
    11
    11A
    Here I need to get the Maximumrecord is " 11A ". But is displaying "9".
    This is my actual quey to finding the maximum number using cost funciong. How ca I apply your method for this query
    Code:
      Select Distinct Max(Cost(ItmCode as Int) ) from MyTable
    Please help me
    -- Prasad --

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The problem is that you have character data that you wish to treat as numeric. So to get what you want '9' compared to '11A' should really be ' 9' with spaces in front, compared to '11A', the '9' and all values need to be right justified
    Code:
    create table test (col1 varchar(20)) 
    go
    insert test values ('1')
    insert test values ('6')
    insert test values ('6')
    insert test values ('7')
    insert test values ('9')
    insert test values ('10')
    insert test values ('11')
    insert test values ('11A')
    go
    
    SELECT MAX(col1),MAX(RIGHT(SPACE(3)+ col1,3)) FROM test
    
    OUTPUT
    
    -------------------- ----
    9                    11A
    MCDBA

  5. #5
    Join Date
    Feb 2002
    Location
    India
    Posts
    38
    very very Thanks
    It is working fine
    -- Prasad --

Posting Permissions

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