SQL Server Performance

T-SQL commands in Stored Procedure!

Discussion in 'SQL Server 2005 General Developer Questions' started by avt2k7, Jun 16, 2007.

  1. avt2k7 New Member

    Hello all,

    I got a T-SQL questions to add more codes for checking the existed data in SQL server tables about 700000 records and following the ordering criteria to update existed data in the table.

    My sample table as following:

    ID | lastname | firstname | school | examscore | areacode | grade |
    1 | Snow | Dun | east | 80 | 1 | |
    2 | Tom | Hellman | west | 100 | | |
    3 | Julie | Williams | east | | | |
    4 | Jen | Lee | | | | |

    ...


    First check whether or not any null values in school, examscore, and areacode?
    Then updated data in grade column followed the belowed ordering rules:

    1. If school, examscore and areacode are not null; then need to update grade = A
    2. If school, examscore are not null and areacode is null; then need to update grade = B
    3. If school is not null; examscore and areacode are null; then need to update grade = C
    4. If all school, examscore and areacode are null; then need to update grade = D

    I am not sure the SQL commands that implement above conditions. Any actual T-SQL codes is much appreciated. Thanks in advance.


  2. khtan New Member

    Rule 1 :


    update t
    set grade = 'A'
    from table
    where examscore is not null
    and areacode is not null


    Rule 2 :


    update t
    set grade = 'B'
    from table
    where examscore is not null
    and areacode is null



    KH
  3. rcp New Member

    Is there a SQL command that will show a list of tables in a database. I just want to run a TSQL statement in an OSQL or SQLCMD environment inside MS DOS

    rcp
    tnx for all your guys help
  4. FrankKalis Moderator

    You should better start a new thread with a different subject than hijacking a thread. <b>[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]</b><br />Do <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT *<br /> FROM INFORMATION_SCHEMA.TABLES<br /> WHERE TABLE_TYPE = 'BASE TABLE'<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. 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 khtan</i><br /><br />Rule 1 :<br /><pre id="code"><font face="courier" size="2" id="code"><br />update t<br />set grade = 'A'<br />from table<br />where examscore is not null<br />and areacode is not null<br /></font id="code"></pre id="code"><br /><br />Rule 2 :<br /><pre id="code"><font face="courier" size="2" id="code"><br />update t<br />set grade = 'B'<br />from table<br />where examscore is not null<br />and areacode is null<br /></font id="code"></pre id="code"><br /><br /><hr noshade size="1"><br /><font color="blue"><font size="1"><i>KH</i></font id="size1"></font id="blue"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You have forgotten the school criteria. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />..but the way to go should be clear. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  6. rcp New Member

    OK thanks for the advice.
  7. Madhivanan Moderator

    Also run sp_help

    Madhivanan

    Failing to plan is Planning to fail
  8. Madhivanan Moderator

    Frank, can you hightlight the change you made?

    Madhivanan

    Failing to plan is Planning to fail
  9. FrankKalis Moderator

    Unfortunately not. <br />But I can tell...<br /><br />I forgot the ) in the smiley tag, so it looked like this [:] I changed it to [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. techy123 New Member

    You can execute Sp_tables command or use following query
    select * from sysobjects WHERE xtype = 'U' ------- U refers to User tables

Share This Page