ANSI warnings, NOCOUNT and Temp Tables

So, I'm always needing these code bits. And yet, I forget the exact syntax and I have to look all over the place for the exact code. Well, here it is. First, NOCOUNT. If you don't want SQL Server to return the Row Count after you execute a query, here is the code:

SET NOCOUNT ON

Next, do you get annoying ANSI errors when inserting data? Begone!

SET ANSI_WARNINGS OFF

Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won't have the table info you're looking for.

-- Won't Work!
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable')
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END

You need this:

-- THis will delete your temp table
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL
BEGIN
Truncate table ##tmpTable
DROP TABLE ##tmpTable
END