[marvinmartian]

is it possible to convert this 2 statements into a single query

UPDATE HR_Appointment
SET CurAppt=(case when (CONVERT(VARCHAR(10),Apptdateto,111))<=(CONVERT(VARCHAR(10),getdate(),111) THEN 0 ELSE 1 END)
WHERE CurAppt=1 and (dbo.Fn_GetTenure(StatusIdx)<>'Permanet') and CtrlNo=477

UPDATE HR_PRPROFILE
SET RemarksIdx=2
WHERE CtrlNo=477 

[e_i_pi]

Yes this is entirely possible using tranactions. Transactions can be used for any type of query in fact, but they are generally most useful when updating and inserting.

The general syntax of trasnactions is:

BEGIN TRANSACTION
-- Put your first query here
-- Put your second query here
-- etc
COMMIT TRANSACTION
GO

If you are using MS SQL, you may need to seperate your queries with semi-colons (not sure on this one).

There is also a command called ROLLBACK, which will allow you to commence a transaction, and if any errors occur, the entire transaction can be rolled back. This means you can prevent data in your database from being corrupted.

It would be very easy to write a long post about error handling in T-SQL, but luck has it someone has done this already on MSDN. Here is the link:
Error Handling in T-SQL: From Casual to Religious

This post has been edited by e_i_pi: 21 November 2011 - 06:37 PM

[marvinmartian]

many many thanks...