18 November 2008

Using T-SQL to Simplify Coding

A sign of a good web developer is determined by his knowledge of SQL. Generally speaking if you know SQL well (be it SQL Server or MySQL) chances are that most of your coding will be reduced dramatically saving you lots of time and also increasing website performance.
Take for example if you wanted to change the status of a registered user from Active to De active or vice verse on the click of a single button and you are using a int field for it in the database( 0 and 1). Now as a novice what I used to do is run a SELECT query and get the user status field. Then compare the status in client side scripting language, and then again run a Update query to change the user status.
But as i studied more on SQL i found out that this could be done just by using a single query/stored procedure and only passing the id of the user to the query as follows(for SQL Server):

Create Procedure changeStatus
@ID integer
BEGIN
Declare @status integer
Select @status= status from UserTable where UserId=@ID
if @status=1
Update UserTable set status=0 where UserId=@ID
ELSE
Update UserTable set status=1 where UserId=@ID
END
END

So you see this has saved me a lot of time and coding. Also modification also becomes easy. Hence if you still think that SQL knowledge is not that important for Development; its time to wake up and brush up on YOUR SQL SKILLS!!!!!!

No comments: