Adding critical logging to your procedures

Here is a little tip that will help produce detailed logging and debugging reference points:

Output the name of the stored procedure when it runs.  Simple.

Procedure names, much like anything else, can change over time.  So how do we keep up changes to those messy output strings?  You don't, Simple again.

Include this little jewel with your print output or logging output and see how easy it is to find your way back to the stored proc:
SELECT OBJECT_SCHEMA_NAME(@@PROCID) as [schema], OBJECT_NAME(@@PROCID) as [procedure]
Maybe really show off what a fancy coder you are by including it in your error handling:
BEGIN CATCH
     SELECT
     OBJECT_SCHEMA_NAME(@@PROCID) as [schema],
     OBJECT_NAME(@@PROCID) as [procedure],    
     ERROR_NUMBER() AS ErrorNumber,     
     ERROR_SEVERITY() AS ErrorSeverity,     
     ERROR_STATE() AS ErrorState,     
     ERROR_PROCEDURE() AS ErrorProcedure,     
     ERROR_LINE() AS ErrorLine,     
     ERROR_MESSAGE() AS ErrorMessage;
END CATCH;   

Nice article about running totals against aggregated row items in SSRS

http://mangalpardeshi.blogspot.com/2009/02/running-totals-in-sql.html

QUOTENAME : A little known util function for "bracketing" a value

DECLARE @TEXT VARCHAR(50)
DECLARE @NUMBER INT
DECLARE @QUOTE CHAR(1)

SET @QUOTE = '"'

SET @TEXT = 'hello'

SET @NUMBER = 13

--bracketed with default brackets

SELECT  QUOTENAME(@TEXT), len(@TEXT), len(quotename(@TEXT))

--bracketed with default brackets

--note that an implicit conversion occurs)

SELECT  QUOTENAME(@NUMBER)

--bracketed with a double quote

SELECT  QUOTENAME(@TEXT, @QUOTE)

Great SQL Brain Teaser.... how many records are in the table?

Create Table TBL1 (col1 int, col2 int)

Create Table TBL2 (col1 int, col2 int)

 

--query1

select count(col1) from TBL1 where col2 >= 5

--results = 3

 

--query2

select count(col1) from TBL1 where col2 < 5

--results = 2

 

-- can you tell how many records are in the table?

-- how/why?

Find the most recently modified table constraints

USE somedatabasename
GO

SELECT DISTINCT CONSTRAINT_NAME

                                 ,modify_date
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT OUTER JOIN sys.all_objects
ON CONSTRAINT_NAME = name
WHERE TABLE_CATALOG = N'somedatabasename'
ORDER BY modify_date DESC

--complements to Igor Malkiman of Qwest Communications for this code

When were the stored procedures last modified?

USE [SOMEdbNAME]
GO
SELECT so.name, so.modify_date
FROM SYS.OBJECTS SO
WHERE SO.name in
   (SELECT ROUTINE_NAME
   FROM INFORMATION_SCHEMA.ROUTINES isr
   WHERE isr.ROUTINE_CATALOG='SOMEdbNAME')
ORDER BY so.modify_date desc

--complements to Igor Malkiman of Qwest Communications for this code

Deleting records from a source table as you add them to a destination

Frequently we come across a case where the records in a source table must meet a certain condition in order to be loaded into the destination table.  I recently came across a problem where the records were to remain in the source table if the conditions were not met. 

So the question was how to keep track of the inserted records? 

• Query the source table after the insert for records matching the destinationtable and then delete them? 
• Create a #Temp table of what is to be inserted and join that same table to the source for deletion?

Or:

• Use the OUTPUT clause (available on insert, update and delete statements) to populate a table of record-keys of what has been inserted and later use that to join to the source records for deletion

Here is an example:

CREATE TABLE #T1(COL1 INT)
CREATE TABLE #T2(COL1 INT)
CREATE TABLE #T3(COL1 INT)

DECLARE @iRec int

TRUNCATE TABLE #T1
TRUNCATE TABLE #T2
TRUNCATE TABLE #T3

-- populate some data 
set @iRec= 1
While @iRec <= 10
BEGIN
   INSERT #T1 VALUES(@iREC)
   if @iRec < 6
      insert #T2 values(@iREC)
   set @iRec = @iRec + 1
END

-- perform the deltion
DELETE #T1
      OUTPUT Deleted.*  INTO #T3
FROM #T1 ONE
JOIN #T2 TWO ON ONE.COL1 = TWO.col1

-show the results
SELECT '#T1 - original recs',* FROM #T1
SELECT '#T2 - deleted recs ',* FROM #T2
SELECT '#T3 - new table ',* FROM #T3

-- cleanup
DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3