Preview affected records? 3

[NuJoizey]

I used to do a lot of MS Access development, and there was a neat feature that allowed you to view the recordset that was to be affected by a DELETE or UPDATE statement before you actually executed it. This was handy, because it allowed you to make sure you weren't about to mangle the wrong set of records because of some careless error.

I haven't run across any similar feature in SQL Server 2005 - does it exist somewhere?

 

[SQLSister]

Generally I write the statment so I can selct from it first to check. something like:

delete a
--select * 
from table1 a 
where field1 = 'test'

or
delete a
--select *
from table1 a
join table2 b on a.myid = b.myid
where b.field1 = 'test'
[/code]



"NOTHING is more important in a database than integrity." ESquared

 

[RiverGuy]

Another thing you can do is utilize a transaction to check out the affects of your query

BEGIN TRANSACTION

  DELETE SomeTable WHERE Column1 = 1
  SELECT * FROM SomeTable --records with Column1 = 1 should be gone

ROLLBACK TRANSACTION

  SELECT * FROM SomeTable --records with Column1 = 1 should still be there as the transaction has been rolled back

Then, when you're satisfied with your query, take out the transaction statements or change your rollback to a commit.

 

[NuJoizey]

Thank you both for your helpful replies.

For what I was trying to do the ROLLBACK TRANSACTION accomplished my objective nicely.

 

[markros]

Nice tip, thanks. I was going to suggest to do the select first, but I like the transaction tip much better.

 

[NuJoizey]

one question,

take out the transaction statements or change your rollback to a commit.

What is the difference between explicitly issuing the COMMIT TRAN statement and just commenting out the ROLLBACK TRAN statement. I assume in this case they both will cause SomeTable to be deleted, no?

 

[RiverGuy]

If you comment out ROLLBACK TRANSACTION, you also have to comment out BEGIN TRANSACTION. That being said, they will accomplish the same thing since your example is performing that one delete action.

Now, if you have multiple statements you are executing, wrapping them in a transaction will be a different ballgame since you could have a failure in one place and wish to rollback everything else--sort of an "All or nothing" scenario.

 

[NuJoizey]

interesting. I noted that SQL server will in fact run the code without the COMMIT TRANSACTION statement, but will not open the table. it says "executing query, waiting for response from data source" and eventually times out.

if you rerun the script with a ROLLBACK or COMMIT, then it will open.

So the table appears to sit kind of in limbo until some sort of closing action is taking.

 

[RiverGuy]

So the table appears to sit kind of in limbo until some sort of closing action is taking.

Yes, in a way that is true. Take the old banking account example. You have two accounts. You wish to transfer $100 from account 1 to account 2. First, $100 is withdrawn from account 1. Next, $100 is added to account 2. If the insert to account 2 fails, you don't want to end up with a missing amount of $100. So, you wrap everything up into a transaction and code it so that it everything either succeeds or fails together.

Now, if someone tries to execute a query while all of this is happening, it would be best that their results "wait" until your transaction is finished. After all, if the insert into account 2 fails, you want to display the original amount as if nothing happened. You don't want anyone getting what is called a "dirty read." So SQL Server may place locks on your objects.

You should do some reading for the specifics:

http://msdn.microsoft.com/en-us/library/ms188929.aspx

 

[NuJoizey]

most helpful. I also re-read the chapter in my WROX sql server programming book about it. I had known about transactions and rollbacks, but never really truly used them until today.

In the bank account example, what would the TSQL structure look like - i mean how would you tell it:

IF Insert into Accout2 Fails THEN
ROLLBACK the whole transaction
ELSE
COMMIT

I'm guessing you couldn't just put it in an IF block...

 

[RiverGuy]

In SQL Server 2005 and above, you can use TRY/CATCH for error handling.

Take a look at this article:

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Scroll down to the Using TRY…CATCH in a transaction section.

 

[NuJoizey]

ah, so it is more straightforward than I thought. I didn't know TRY..CATCH existed in TSQL

Anyway, thank you for this extremely useful information.

Great thread! I'm much obliged.

 

[markros]

I googled for Kevin Goff TRY CATCH (because I remember Kevin's good article on the subject) and found this instead

http://www.windowsitlibrary.com/Content/2052/03/1.html#1

Very interesting stuff.

 

[markros]

Here is the article (I saw it published in different format before), but this one is OK too

http://www.setfocus.com/TechnicalArticles/sql-server-2005-tsql-2.aspx

I used very similar code in my SPs for TRY/CATCH.

 

[TysonLPrice]

If the table is not very large I'll copy it to a temp table and "play" with that first. For example:

Select * into #Temp from SomeTable

(or ##Temp for testing from a VB application)

Then drop it when you are done.