How to Debug Stored Procedure / SQL Code

In below video i explain how to debug SQL code,

SQL material for your practice,

Select ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED

from INFORMATION_SCHEMA.ROUTINES

where (CREATED > (Select getdate()-7) OR LAST_ALTERED > (Select getdate()-7))

Create Table ObjectsModified

(

Id Int Identity(1,1),

ROUTINE_NAME  Varchar(255),

ROUTINE_TYPE  Varchar(15),

CREATED DateTime,

LAST_ALTERED DateTime,

DateTimeFlag DateTime Default getdate(),

messagestr Varchar(50)

)

GO

---------------------------procedure

Create Procedure RetriveListModifiedObjects

(@Days Int)

As

Begin

Truncate Table ObjectsModified

Create Table #Temp

(

Id Int Identity(1,1),

ROUTINE_NAME  Varchar(255),

ROUTINE_TYPE  Varchar(15),

CREATED DateTime,

LAST_ALTERED DateTime

)

Insert into #Temp

(

ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED

)

Select ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED

from INFORMATION_SCHEMA.ROUTINES

Declare @createddate DateTime

Declare @lastaltereddate DateTime

Declare @objectname Varchar(255)

Declare @ObjectType Varchar(15)

While Exists(Select ROUTINE_NAME from #Temp)

Begin

Select Top 1 

@objectname=ROUTINE_NAME,

@ObjectType=ROUTINE_TYPE,

@createddate=CREATED,

@lastaltereddate=LAST_ALTERED 

from #Temp

if (@createddate>(Select GETDATE()-@Days) OR @lastaltereddate>(Select GETDATE()-@Days))

Begin

Insert into ObjectsModified

(

ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED

)

Select @objectname,@ObjectType,@createddate,@lastaltereddate

end

Delete From #Temp where ROUTINE_NAME=@objectname and ROUTINE_TYPE=@ObjectType

End

Exec child_RetriveListModifiedObjects @Days

Select * from ObjectsModified

End ---------------main procedure end 

----child procedure

Create Procedure child_RetriveListModifiedObjects

(@Days Int)

As

Begin

Update ObjectsModified Set messagestr='Object modified in last '+Convert(varchar(5),@Days) +' Days'

end

SP execute statement  

Exec RetriveListModifiedObjects 15