Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2002
    Posts
    6

    Unanswered: Transferring Procedures from SQL Server 7 to Sybase

    The following procedure works well with MS SQL server 7.0>When i tried to create the same procedure in Sybase it is showing me a list of errors...any idea???

    THE PROCEDURE:
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    create procedure getclaims_allemp_new

    @policy char(5),

    @datefield_to_pick char(1),

    @date_from datetime,

    @date_to datetime,

    @field_on_sort char(1)

    as



    declare @selectpart nvarchar(2000)

    declare @orderbypart nvarchar(400)

    declare @completesql nvarchar(2500)



    if (@datefield_to_pick = '1')

    begin

    set @selectpart = N'select tclaim.policy,tclaim.claim_no,tclaim.nric_cert_no, ' + char(13)

    set @selectpart = @selectpart + N'tclaim.sub_office,tclaim.insured_name,tclaim.cla imant_name,' + char(13)

    set @selectpart = @selectpart + N'tdepmst.dep_desc,tlocmst.loc_desc,tben_desc.bene fit_desc,' + char(13)

    set @selectpart = @selectpart + N'tclaim.incurred_date,tclaim.payment_date,' + char(13)

    set @selectpart = @selectpart + N'tclaim.presented_amt,tclaim.adjusted_amt' + char(13)

    set @selectpart = @selectpart + N'from (((tclaim' + char(13)

    set @selectpart = @selectpart + N'inner join tdepmst on tclaim.dependent_code = tdepmst.dependent_code)' + char(13)

    set @selectpart = @selectpart + N'inner join tlocmst on tclaim.loc_code = tlocmst.loc_code )' + char(13)

    set @selectpart = @selectpart + N'left outer join tben_desc on tclaim.benefit_code = tben_desc.benefit and tclaim.loc_code = tben_desc.loc_code)' + char(13)

    set @selectpart = @selectpart + N'where tclaim.policy = @in_policy ' + char(13)

    set @selectpart = @selectpart + N'and tclaim.payment_date between @in_date_from and @in_date_to' + char(13)

    set @selectpart = @selectpart + N'and tclaim.payment_date <> NULL ' + char(13)



    end

    else

    begin

    set @selectpart = N'select tclaim.policy,' + char(13)

    set @selectpart = @selectpart + N'tclaim.claim_no,tclaim.nric_cert_no,tclaim.sub_o ffice,tclaim.insured_name,tclaim.claimant_name,' + char(13)

    set @selectpart = @selectpart + N'tdepmst.dep_desc,tlocmst.loc_desc,tben_desc.bene fit_desc,' + char(13)

    set @selectpart = @selectpart + N'tclaim.incurred_date,tclaim.payment_date,' + char(13)

    set @selectpart = @selectpart + N'tclaim.presented_amt,tclaim.adjusted_amt' + char(13)

    set @selectpart = @selectpart + N'from (((tclaim' + char(13)

    set @selectpart = @selectpart + N'inner join tdepmst on tclaim.dependent_code = tdepmst.dependent_code)' + char(13)

    set @selectpart = @selectpart + N'inner join tlocmst on tclaim.loc_code = tlocmst.loc_code)' + char(13)

    set @selectpart = @selectpart + N'left outer join tben_desc on tclaim.benefit_code = tben_desc.benefit and tclaim.loc_code = tben_desc.loc_code)' + char(13)

    set @selectpart = @selectpart + N'where ' + char(13)

    set @selectpart = @selectpart + N'tclaim.policy = @in_policy ' + char(13)

    set @selectpart = @selectpart + N'and tclaim.incurred_date between @in_date_from and @in_date_to' + char(13)

    set @selectpart = @selectpart + N'and tclaim.payment_date <> NULL ' + char(13)

    end

    if (@field_on_sort = '0')

    begin

    set @orderbypart = N'order by tclaim.insured_name,tclaim.payment_date,tclaim.dep endent_code ,tclaim.loc_code'

    end

    if (@field_on_sort = '1')

    begin

    set @orderbypart = N'order by tclaim.insured_name,tclaim.payment_date,tclaim.dep endent_code ,tclaim.loc_code'

    end

    if (@field_on_sort = '2')

    begin

    set @orderbypart = N'order by tclaim.loc_code,tclaim.insured_name,tclaim.depende nt_code,tclaim.payment_date'

    end

    if (@field_on_sort = '3')

    begin

    set @orderbypart = N'order by tclaim.incurred_date,tclaim.insured_name,tclaim.de pendent_code,tclaim.loc_code'

    end

    if (@field_on_sort = '4')

    begin

    set @orderbypart = N'order by tclaim.sub_office,tclaim.insured_name,tclaim.depen dent_code,tclaim.payment_date'

    end



    set @completesql = @selectpart + @orderbypart







    execute sp_executesql @completesql,

    N'@in_policy varchar(5),@in_datefield_to_pick varchar(1),

    @in_date_from datetime,@in_date_to datetime,@in_field_on_sort varchar(1)',

    @policy ,@datefield_to_pick ,

    @date_from ,@date_to ,@field_on_sort
    THE ERRORS FROM SYBASE Procedure 'getclaims_allemp_new', Line 21:
    Length or precision specification 2500 is not within the range of 1 to 255.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 29:
    Incorrect syntax near '@selectpart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 61:
    Incorrect syntax near '@selectpart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 93:
    Incorrect syntax near '@orderbypart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 101:
    Incorrect syntax near '@orderbypart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 109:
    Incorrect syntax near '@orderbypart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 117:
    Incorrect syntax near '@orderbypart'.
    Server Message: Number 102, Severity 15
    Procedure 'getclaims_allemp_new', Line 125:
    Incorrect syntax near '@orderbypart'.
    Server Message: Number 137, Severity 15
    Procedure 'getclaims_allemp_new', Line 139:
    Must declare variable '@completesql'.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    What version of Sybase ASE?
    Thanks,

    Matt

  3. #3
    Join Date
    Sep 2002
    Posts
    6
    I don't know the version of sybase,all i know is that it runs on unix and i am connecting to it using sybase anywhere..
    I think it is sybase 11.

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Many things are different in the old version of Sybase. Dynamic SQL is not implemented as such, nor does it support character fields over 255 characters. Why are you using such an old version?
    Thanks,

    Matt

  5. #5
    Join Date
    Sep 2002
    Posts
    6
    My client uses that ,and they have a lot of applications running against this sybase database.

    As u said,sybase old version can't support varchar over 255,right?Then one more thing is that,the joins can't be specified as inner join on table but to use *= or =*,right??

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Correct on both counts. You may be able to get by with the TEXT datatype.
    Thanks,

    Matt

  7. #7
    Join Date
    Sep 2002
    Posts
    6
    matt,
    The version is 11.9
    Does this version support dynamic SQL as well as the long varchars??

    Regards,
    Naveen

  8. #8
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Not that I am aware of.

    http://www.sypron.nl/dynsql.html
    http://www.sypron.nl/dynsqlcis.html

    That SQL Stored Procedure is not very nice, you could tie it into several stored procs.
    Thanks,

    Matt

  9. #9
    Join Date
    Sep 2002
    Posts
    6
    what is this matt??

    'severas stored procs.'

  10. #10
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    nmt

    You have a number of issues:
    1 There are many differences between MS and Sybase. MS tends to support a variety of wierd facilities so that its other software can work with it. There are more simple, straight-forward ways of doing things.
    2 The proc takes advantage of MS peculiarites. In many ways, the code is not ANSI-compliant and therefore you cannot expect to migrate it easily (or to execute the first time, or to give you the same result set).
    3 Even if you got around the first set of error messages, there will be another set after that, and so on.
    4 The proc is very badly written and can be easily re-structured and improved to provide less problems and to isolate errors. You are better off understanding what the code is supposed to do and rewriting it in ANSI-compliant form and with good structure and standards.
    5 You do not need to use dynamic SQL for this proc (a proc is actually compiled code that executes much faster than dynamic SQL; a proc that contains dynamic SQL like this is not a 'proc', it is merely a method of handling multiple query options). Eight procs would be better. Even one proc with eight [2 WHEREs X 4 ORDER BYs] SELECTS would be better.
    6 The INNER JOINs can be replaced with simple subqueries. The OUTER JOINs can be replaced with '*=' and '=*' (but note that MS and Sybase give you different result sets depending on Null values)
    Last edited by DerekA; 09-17-02 at 22:33.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  11. #11
    Join Date
    Sep 2002
    Posts
    17

    Question

    NMT:

    I have a similar structure in my MS SQL DB and it works well.

    MattR / DerekA:

    I am curious as to why you reccommend breaking up the dynamic SQL building inside a proceedure into multiple proceedures ( from a theoretical level not specific to the above proceedure ).

    My experience has shown this to be an efficent way of managing and controlling data to the middle tier object ( which provides data authentication down to the proceedures ).

    Given the 3 tier structure of:

    Presentation (ASP/PHP/???)
    Business Logic (COM)
    Data (SQL)

    Using dynamic proceedures with options provides for much cleaner data flow, better control and shorter development cycles.

    For example, if you want to add a sort order to the above proc you merely add an option in the proceedure at the DATA layer and modify the PRESENTATION Layer appropriately and away you go. No need to mess around with the COM Object which can be a pain to deploy.

    I can see that theroetically from pure speed it would not be optimal but we have not noticed any degredation in our environment.
    David Williams
    Senior Jack of All Trades

  12. #12
    Join Date
    Sep 2002
    Posts
    6
    Hi All...,
    I was earlier having the same procedure split into 5 different procedures but it was very difficult to maintain and since the system was on SQL Server there was not need to check for the T SQL compliance.But now the company has decided to port the database to Sybase.

    Thanks for you response.

  13. #13
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The reason why is that ASE does not compile stored procs very well.

    If you use conditional logic ASE will make a query plan based upon the first execution.

    So, if you have something like this:

    Code:
    CREATE PROCEDURE foo
      @decision INT
    AS
    
      IF @decision = 1
        SELECT *
          FROM bar
         WHERE col1 = 123
           AND col2 = 'J'
      ELSE
        SELECT *
          FROM table1
         WHERE something = 'Joe'
           AND happy     = @decision
    GO
    If you pass in @decision = 1 then the second plan will never be cached, stats not taken, etc. This behavior was current as of 11.9.2 -- I don't know of 12.5 but I hope it is fixed at some point.

    Code:
    CREATE PROCEDURE foo
      @decision INT
    WITH RECOMPILE
    AS
    
      IF @decision = 1
        EXEC first_sql
      ELSE
        EXEC second_sql @decision
    GO
    Thanks,

    Matt

  14. #14
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Originally posted by dwilliams
    NMT:

    MattR / DerekA:

    I have a similar structure in my MS SQL DB and it works well.
    MS SQL Server and Sybase ASE are very different animals for many years now, there is no real basis for comparing the two. The phylosophy, style and attitudes are quite different.

    I am curious as to why you reccommend breaking up the dynamic SQL building inside a proceedure into multiple proceedures ( from a theoretical level not specific to the above proceedure ).

    My experience has shown this to be an efficent way of managing and controlling data to the middle tier object ( which provides data authentication down to the proceedures ).

    Given the 3 tier structure of:

    Presentation (ASP/PHP/???)
    Business Logic (COM)
    Data (SQL)

    Using dynamic proceedures with options provides for much cleaner data flow, better control and shorter development cycles.

    For example, if you want to add a sort order to the above proc you merely add an option in the proceedure at the DATA layer and modify the PRESENTATION Layer appropriately and away you go. No need to mess around with the COM Object which can be a pain to deploy.

    I can see that theroetically from pure speed it would not be optimal but we have not noticed any degredation in our environment.
    In summary:
    1 In general, if you want scalability (I have yet to find a successful application that does not REQUIRE scalability after implementation) you have to spend some effort ensuring your objects in all three tiers are scalable. Scalability and high performance does not come free.
    2 From your comments you are clearly 1- and 2-tier-centric. This is fine but the real price will be paid in the future when the 3rd tier is not performing under load.
    3 Think about this. Dynamic SQL has to be parsed, allocated and executed 'fresh' every time. Compiled stored procedures execute as much as 30 times faster (depending on complexity, etc) and statistics and current procedure cache content (eg. query plans and query trees) can be taken advantage of.
    4 Just as much as you want control & ease of admin on the first & second tier, one day you (or the DBA you dump your "working" application on, will want control & admin on the third tier. Dynamic SQL is difficult to see, control &administer; sprocs are simple to identify, control & administer. And the development "cost" is small. Once in production (assuming you have decent server admin and monitoring tools), administering 1000 rather than 100 sprocs is easy.
    5 I can see your comments relate to speed of development and ease of management in the middle tier, but I disagree that you have addressed "controlling data" or data efficiency (caching, sorting, etc.) at all in the middle tier.
    6 Due to our many projects and performance tuning assignments, we have standards for both 2- and 3-tier development. Some new developers do not like the standards initially but love them after they settle into them:
    1. No dynamic SQL in production (ok for development and test)
    2. No ORDER BY on the server (this is simple to do in the first and second tier, ie. the client system or the COM object; this is a PRESENTATION issue)
    3. all data-related business/transaction logic in SQL procs with full transaction control.
    4. all presentation logic on the client (simply click on the column heading for sorting on that column; use a default sort order)
    5. the developer is held responsible for all they write, icluding the SQL on the server

    7 Just using the example above (and I am only asking for (a) here), all you have to do is copy, paste and edit one line in each of 8 SELECTs. You can have one proc with 8 SELECTs (simple) or one proc calling 8 procs (more performance) with 1 SELECT each; no change to the calling app.
    If you add a new sort order, copy, paste and edit one more line. What's the big deal ?
    If you have SQL development tools eg. Rapid SQL, SQL Programmer), the copy/paste/edit is reduced to clicks.
    8 You have to balance short development cycles against performance on the server.
    Last edited by DerekA; 09-19-02 at 20:42.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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