Hi

Yesterday I came across a very common but interesting requirement from a friend. Actually the requirement is very common for developer dealing with the database. Many a times a we have some SQL, TSQL code which we use during development but that should not run on the server.

Normally developers have a process of checking from the process prospective so that the code does not go on the server. But mistakes are inevitable. So we should also have a solution for the same from programmatic aspect. This is very important because some code (Like delete, or update statement) can cause big problem in the live site if they run by mistake also.

We should have a process to stop such error, but we can also take small care in the code itself to prevent any major problem. Some of the way are discussed below.

If we are sure of the production server name then we can have a small IF statement based on the server instance and return from the procedure if the server is the production server.

IF @@SERVERNAME = 'Vikram'
BEGIN
     RETURN
END

You could have also checked that if the server is development then only allow the code to run other wise return. If the database name of the production and development server are different that we can use the database name for the return statement.

This code should be used in some of the critical procedures, like if you have a procedure to delete all the data from the database for testing purpose in the development. These are small prevention that can stop major problem in the live server.

Vikram