ITDevSpace - Development Tips & Tricks

This is improved version of the script from 2009 using try/catch syntax USE [MyDatabase] GO PRINT ' DROP PROCEDURE [usp_mysproc] ' GO if exists ( select * from dbo.sysobjects where id = object_id(N' [dbo].[usp_mysproc] ') and OBJECTPROPERTY(id, N' IsProcedure ') = 1) drop procedure [dbo].[usp_mysproc] GO PRINT ' CREATE PROCEDURE [usp_mysproc] ' GO CREATE PROCEDURE [dbo].[usp_mysproc] ( @RaiseError bit = 0 ) AS BEGIN TRY BEGIN TRAN IF @RaiseError = 1 BEGIN DECLARE @ TEMP TABLE ( Column 1 varchar (2)) INSERT INTO @ TEMP ( Column 1) VALUES (' 123 ') END COMMIT TRAN END TRY BEGIN CATCH DECLARE @ErrorInfo VARCHAR (4000) SET @ErrorInfo = ' ERROR [ '+LTRIM(STR(ERROR_NUMBER()))+' ][ '+ERROR_PROCEDURE()+' : '+LTRIM(STR(ERROR_LINE()))+' ] - ' + ERROR_MESSAGE() PRINT CONVERT ( VARCHAR ,GETDATE(),21) + ' : '+@ErrorInfo ROLLBACK

DECLARE @ TEMP TABLE ( Ticker varchar (50), Date int , Details varchar (50) ) INSERT INTO @ temp SELECT ' A ',20110101,' A-1 ' UNION SELECT ' B ',20110101,' B-1 ' UNION SELECT ' A ',20110102,' A-2 ' UNION SELECT ' C ',20110102,' C-2 ' --Select whole universe SELECT * FROM @ temp a ORDER BY a.Ticker --Select latest data (can't include Details) SELECT a.Ticker, MAX (a. Date ) Date FROM @ temp a GROUP BY a.Ticker ORDER BY a.Ticker --Select data with Details (no longer latest) SELECT a.Ticker, MAX (a. Date ) Date ,Details FROM @ temp a GROUP BY a.Ticker,Details ORDER BY a.Ticker --Select latest data (with Details) SELECT a.Ticker,a.Details,a. Date FROM @ temp a inner join @ temp b ON a.Ticker = b.Ticker GROUP BY a.Ticker, a.Details, a. Date HAVING a. Date = MAX (b. Date ) ORDER BY a.Ticker