SQL Server Performance Forum – Threads Archive

T-SQL commands in Stored Procedure!

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.

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

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

You should better start a new thread with a different subject than hijacking a thread. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Do <pre id="code"> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE' </pre id="code"> – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by khtan Rule 1 : <pre id="code"> update t set grade = ‘A' from table where examscore is not null and areacode is not null </pre id="code"> Rule 2 : <pre id="code"> update t set grade = ‘B' from table where examscore is not null and areacode is null </pre id="code"> <hr noshade size="1"> KH <hr height="1" noshade id="quote"></blockquote id="quote"> You have forgotten the school criteria. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] ..but the way to go should be clear. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

OK thanks for the advice.

Also run sp_help Madhivanan Failing to plan is Planning to fail

Frank, can you hightlight the change you made? Madhivanan Failing to plan is Planning to fail

Unfortunately not. But I can tell… I forgot the ) in the smiley tag, so it looked like this [:] I changed it to [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’

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

]]>