SQL Procedure Syntax

      /****** REPORT ::: Sample SP written by Arka Gupta ******/

     

      IF EXISTS(SELECT * FROM SysObjects WHERE NAME = 'ARKA_SP' AND XTYPE = 'P')

BEGIN

      DROP PROCEDURE ARKA_SP

END

GO

      SET ANSI_NULLS ON

GO

      SET NOCOUNT ON

GO

      SET QUOTED_IDENTIFIER ON

GO

      SET XACT_ABORT ON

GO

      CREATE PROCEDURE ARKA_SP

      (@FROMDATE VARCHAR(8),

       @TODATE VARCHAR(8))

      AS

      BEGIN

     

            BEGIN TRY

                  BEGIN TRAN TRN1

                 

                        CREATE TABLE #TEST

                        (ID INT PRIMARY KEY,

                         NAME VARCHAR(10),

                         MARKS VARCHAR(2)

                        )

                        INSERT INTO #TEST

                        SELECT 1,'AAA','10'

                        UNION ALL

                        SELECT 2,'BBB','20'

                        UNION ALL

                        SELECT 3,'CCC','30'

                        UNION ALL

                        SELECT 4,'DDD','40'

                        UNION ALL

                        SELECT 5,'EEE','50'

                       

                  COMMIT TRAN TRN1

            END TRY

           

            BEGIN CATCH

                  ROLLBACK TRAN TRN1

            END CATCH        

           

            SELECT * FROM #TEST

           

      END

--    EXEC ARKA_SP '20140120','20140205'