SQL Examples

This page gives you for learning on Structured Query Language and it helps you to perform on SQL commands/queries which provide instant result.

EXAMPLE: 1

How to select the table record by passing page index?

How to search the table record by passing search text for the page index?

CREATE TABLE [dbo].[ErrorLog](

                [ErrorLogID] [int] IDENTITY(0,1) NOT NULL,

                [ProcessID] [int] NOT NULL,

                [ErrorLogDate] [datetime] NULL,

                [ErrorLogNumber] [int] NOT NULL,

                [ErrorLogMessage] [varchar](1000) NULL,

                [ErrorLogFunction] [varchar](50) NULL,

                [ErrorLogClassName] [varchar](30) NULL,

                [ErrorLogLineNumber] [int] NULL,

 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED

(

                [ErrorLogID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Record Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies System Process Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ProcessID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Date of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogDate'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Number of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogNumber'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Message of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogMessage'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Function of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogFunction'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Class Name of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogClassName'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Specifies Line Number of Error Log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogLineNumber'

GO

SET IDENTITY_INSERT [dbo].[ErrorLog] ON

--INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

--VALUES (0, 1, CAST(0x0000A58401478D50 AS DateTime), 1, N'', NULL, N'', 0)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (1, 1, CAST(0x0000A58401478D51 AS DateTime), 1, N'99-Error due to invalid data', NULL, N'com.pp.entirty.Action.js', 23)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (2, 1, CAST(0x0000A58401478D51 AS DateTime), 1, N'99-Error due to invalid data', NULL, N'com.pp.entirty.Action.js', 23)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (3, 1, CAST(0x0000A58401478D51 AS DateTime), 1, N'99-Error due to invalid data', NULL, N'com.pp.entirty.Action.js', 23)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (4, 1, CAST(0x0000A58401478D51 AS DateTime), 99, N'99-Error due to invalid data', NULL, N'com.pp.entirty.Action.js', 23)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (5, 5, CAST(0x0000A58401478D52 AS DateTime), 99, N'8134-Divide by zero error encountered.', N'USP_TP_UserLoginPassword_Validate', NULL, 33)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (6, 5, CAST(0x0000A58401478D52 AS DateTime), 99, N'8134-Divide by zero error encountered.', N'USP_TP_UserLoginPassword_Validate', NULL, 33)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (7, 5, CAST(0x0000A58401478D52 AS DateTime), 99, N'8134-Divide by zero error encountered.', N'USP_TP_UserLoginPassword_Validate', NULL, 30)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (8, 5, CAST(0x0000A58401478D52 AS DateTime), 99, N'8134-Divide by zero error encountered.', N'USP_TP_UserLoginPassword_Validate', NULL, 32)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (9, 5, CAST(0x0000A58401478D53 AS DateTime), 99, N'8134-Divide by zero error encountered.', N'USP_TP_UserLoginPassword_Validate', NULL, 32)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (10, 5, CAST(0x0000A58401478D53 AS DateTime), 99, N'Error 09004: Amount not approved: Your pay amount exceeds the approval limit.', N'USP_TP_Validate', NULL, 32)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (11, 2, CAST(0x0000A5840149E112 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (12, 1, CAST(0x0000A584014AE7A8 AS DateTime), 99, N'547-The DELETE statement conflicted with the REFERENCE constraint "FK_UserChangePasswordHistory_ProcessID". The conflict occurred in database "paypilotdbtest", table "dbo.UserChangePasswordHistory", column ''ProcessID''.', N'USP_TP_Operator_Delete_ByProcessID', NULL, 38)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (13, 2, CAST(0x0000A5840155484C AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (14, 1, CAST(0x0000A5840155AD90 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1105)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (15, 1, CAST(0x0000A5840158F9BB AS DateTime), 1, N'java.lang.NumberFormatException: For input string: ""', NULL, N'GroupSetupController', 1655)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (16, 1, CAST(0x0000A58401614546 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 2456)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (17, 1, CAST(0x0000A58401614903 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 2456)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (18, 1, CAST(0x0000A58401632D56 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1105)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (19, 1, CAST(0x0000A585002F7513 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1105)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (20, 1, CAST(0x0000A58500A13C07 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 707)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (21, 1, CAST(0x0000A58500A15F5B AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 707)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (22, 1, CAST(0x0000A58500A17A87 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 707)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (23, 2, CAST(0x0000A58500C71D2B AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (24, 1, CAST(0x0000A58500D02CD5 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 707)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (25, 1, CAST(0x0000A58500D0711B AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 707)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (26, 2, CAST(0x0000A58500D42813 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (27, 2, CAST(0x0000A58500D42BF4 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (28, 1, CAST(0x0000A58500E4D959 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1105)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (29, 2, CAST(0x0000A58500F64F69 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (30, 2, CAST(0x0000A58500F657AB AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (31, 2, CAST(0x0000A58500FFA33A AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (32, 1, CAST(0x0000A58501072605 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 708)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (33, 1, CAST(0x0000A58501077461 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 708)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (34, 1, CAST(0x0000A585011F7B3B AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 708)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (35, 1, CAST(0x0000A58501207E36 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 215)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (36, 54, CAST(0x0000A58501272838 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1792)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (37, 1, CAST(0x0000A58501276888 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 215)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (38, 2, CAST(0x0000A585012D7C76 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (39, 2, CAST(0x0000A585012DC4FA AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (40, 2, CAST(0x0000A585012DF9FF AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (41, 2, CAST(0x0000A585012DFA26 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (42, 9, CAST(0x0000A585012E5203 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (43, 1, CAST(0x0000A5850131D6C7 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1106)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (44, 9, CAST(0x0000A5850131E3A6 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (45, 9, CAST(0x0000A5850131EE12 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (46, 9, CAST(0x0000A5850131F864 AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (47, 1, CAST(0x0000A5850132DFB4 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'GroupSetupController', 959)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (48, 1, CAST(0x0000A585015DEE12 AS DateTime), 1, N'java.lang.NullPointerException', NULL, N'LoginController', 1106)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

VALUES (49, 1, CAST(0x0000A58501605D06 AS DateTime), 1, N'org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [com.prelude.paypilot.model.SearchDefinition#9]', NULL, N'PaymentsServiceDAOImpl', 3406)

INSERT [dbo].[ErrorLog] ([ErrorLogID], [ProcessID], [ErrorLogDate], [ErrorLogNumber], [ErrorLogMessage], [ErrorLogFunction], [ErrorLogClassName], [ErrorLogLineNumber])

 VALUES (50, 9, CAST(0x0000A58600B6627C AS DateTime), 1, N'Error : null', NULL, N'CustomUserDetailsService', 64)

SET IDENTITY_INSERT [dbo].[ErrorLog] OFF

GO

-- ======================================================

/*

AUTHOR                   : Govind

CREATE DATE  : 22-Aug-2014

DESCRIPTION  : Logging Error.

SELECT * FROM dbo.ErrorLog

SET STATISTICS TIME ON

EXEC [USP_TP_ErrorLog_Insert] 1, '1', 23, 'This Error due to invalid data', 'getAction', 'Action.js'

SET STATISTICS TIME OFF

*/

-- ======================================================

CREATE PROCEDURE [dbo].[USP_TP_ErrorLog_Insert]

                -- Parameters for the stored procedure

                @ProcessID INT,

                @ErrorLogNumber INT = 0,

                @ErrorLogLineNumber INT,

                @ErrorLogMessage VARCHAR(1000) = null,

                @ErrorLogFunction VARCHAR(50) = null,

                @ErrorLogClassName VARCHAR(30) = null

AS

BEGIN

                 SET NOCOUNT ON;

     BEGIN TRY

        

        BEGIN TRANSACTION

                                                 

                                INSERT INTO [dbo].[ErrorLog]

           ([ProcessID]

           ,[ErrorLogDate]

           ,[ErrorLogNumber]

           ,[ErrorLogMessage]

           ,[ErrorLogFunction]

           ,[ErrorLogClassName]

           ,[ErrorLogLineNumber])

                                VALUES

           (@ProcessID

           ,GETDATE()

           ,@ErrorLogNumber

           ,@ErrorLogMessage

           ,@ErrorLogFunction

           ,@ErrorLogClassName

           ,@ErrorLogLineNumber)

                                COMMIT TRANSACTION

                   RETURN -- 'SUCCESS'

     END TRY

               

     BEGIN CATCH

                                 

                     IF @@TRANCOUNT > 0 -- @@ERROR>0

                                                ROLLBACK

                               

            SELECT @ErrorLogLineNumber = ERROR_LINE(),

                                                @ErrorLogFunction = ERROR_PROCEDURE(),

                                                @ErrorLogMessage = CAST(ERROR_NUMBER() AS VARCHAR(15)) + '-' + ERROR_MESSAGE()

        

         INSERT INTO [dbo].[ErrorLog]

           ([ProcessID]

           ,[ErrorLogDate]

           ,[ErrorLogNumber]

           ,[ErrorLogMessage]

           ,[ErrorLogFunction]

           ,[ErrorLogClassName]

           ,[ErrorLogLineNumber])

                                VALUES

           (@ProcessID

           ,GETDATE()

           ,@ErrorLogNumber

           ,@ErrorLogMessage

           ,@ErrorLogFunction

           ,@ErrorLogClassName

           ,@ErrorLogLineNumber

           )

 

                                RETURN --'ERROR'

                                 

     END CATCH

                               

                SET NOCOUNT OFF;

                               

END

GO

-- ======================================================

/*

AUTHOR                   : Govind

CREATE DATE  : 16-JUN-2015

DESCRIPTION  : To get the current page record passing SQL query based on input criteria.

SET STATISTICS TIME ON

EXEC [USP_TP_Table_Query_Record_Get_ByPage]

1,             -- @ProcessID

4,             -- @PageSize

1,             -- @PageIndex

'SELECT * FROM dbo.ErrorLog WHERE ErrorLogDate < GETDATE()',  -- @Query

'99',          -- @SearchValue

'ErrorLogMessage, ErrorLogNumber',             -- @SearchColumns

'*',            -- @SelectColumns  here you can specify only coulumns to be selected like ErrorLogMessage, ErrorLogFunction instead of '*'

'ErrorLogMessage', -- @SortColumn

'ASC',                       -- @SortBy 

'ErrorLog'   -- @SortTable

SET STATISTICS TIME OFF

*/

-- ======================================================

CREATE PROCEDURE [dbo].[USP_TP_Table_Query_Record_Get_ByPage]

                @ProcessID INT,                                                                       -- System Process ID  

                @PageSize INT,                                                                         -- Number of Rows to be returned

                @PageIndex INT,                                                                       -- Index of the page

                @Query VARCHAR(4000),                                           -- SQL Query

                @SearchValue VARCHAR(1000),                  -- Search text from the below defined search columns from SQL Query

                @SearchColumns VARCHAR(4000),             -- Columns to be searched, columns can be specified with comma seperated, search text to be searched for all columns then you specify "*"

                @SelectColumns VARCHAR(4000),              -- Columns to be selected from SQL Query

                @SortColumn VARCHAR(200),                    -- Columns to be sorted

                @SortBy VARCHAR(6),                                                               -- Columns to be sorted either ASC or DESC

                @SortTable VARCHAR(128)                                         -- Column of table to be sorted

AS

BEGIN

                SET NOCOUNT ON;

               

                BEGIN TRY

                DECLARE

    @StartRow INT,

    @EndRow INT,

    @SqlString NVARCHAR(4000),

    @SearchString VARCHAR(4000)

   

    IF(@PageIndex < 1)

    BEGIN

                                SET @PageIndex = 1

                END        

  

    SET @StartRow = (@PageSize * (@PageIndex - 1)) + 1

    SET @EndRow = @PageSize * @PageIndex

                SET @SearchString =''

                IF LEN(@SearchValue)>0

                BEGIN

                                IF LEN(@SearchColumns)>0

                                BEGIN

                                                --SET @SearchString = ' WHERE  StatusLabel.StatusLabelName LIKE ''%' + @SearchValue +'%'''

                                                DECLARE @char VARCHAR(1)

                                                SET @char = ','

                                                DECLARE @sliceColumn varchar(1000)      

                                                DECLARE @idxLoop int

                                                SET @idxLoop = 1

                                                 

                                                IF len(@SearchColumns)<1 or @SearchColumns is null 

                                                                SET @SearchString=''

                                                                      

                                                WHILE @idxLoop!= 0      

                                                                BEGIN      

                                                                                SET @idxLoop = charindex(@char,@SearchColumns)      

                                                                                IF @idxLoop!=0      

                                                                                                SET @sliceColumn = left(@SearchColumns,@idxLoop - 1)      

                                                                                ELSE      

                                                                                                SET @sliceColumn = @SearchColumns       

                                                                                IF(len(@sliceColumn)>0)

                                                                                BEGIN

                                                                                                IF(len(@SearchString)>0)

                                                                                                                SET @SearchString = @SearchString +  + ' OR ' + @sliceColumn + ' LIKE ''%' + @SearchValue +'%'''

                                                                                                ELSE

                                                                                                                SET @SearchString = @SearchString + ' ' + @sliceColumn + ' LIKE ''%' + @SearchValue +'%'''

                                                                                                --print @SearchString      

                                                                                END

                                                                                SET @SearchColumns = right(@SearchColumns,len(@SearchColumns) - @idxLoop)      

                                                                                IF len(@SearchColumns) = 0 BREAK      

                                                                END  

                                                   

                                                IF len(ltrim(rtrim(@SearchString)))>0

                                                                SET @SearchString = 'WHERE (' + @SearchString + ' )'

                                                --PRINT  @SearchString

                                END        

                END

               

                SET @SqlString = '

                SELECT * FROM (

                                SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortTable +'.'+ @SortColumn +' ' + @SortBy +') RowNumber, ' +

                                @SelectColumns +

                                ' FROM '+

                                '(' + @Query + ') ' +

                                                @SortTable + ' ' + @SearchString + '

                                ) tbl__name

    WHERE ((tbl__name.rowNumber Between '+ CAST(@StartRow AS VARCHAR(10)) + ' AND ' + CAST(@EndRow AS VARCHAR(10)) + ' ))'

       

                --PRINT @SqlString

                EXECUTE sp_executesql @SqlString

                               

  

                END TRY

               

                BEGIN CATCH

                                 

                     IF  @@ERROR>0 -- @@TRANCOUNT if Begin commit tran used inside the try catch.

                                                BEGIN

                                                                DECLARE

                                                                @ErrorLogLineNumber INT,

                                                                @ErrorLogNumber INT,

                                                                @ErrorLogMessage VARCHAR(1000) = null,

                                                                @ErrorLogFunction VARCHAR(50) = null                                                      

                                                               

                                                                --DB Error                                 

                                                                SELECT @ErrorLogLineNumber = ERROR_LINE(),

                                                                                @ErrorLogMessage = CAST(ERROR_NUMBER() AS VARCHAR(15)) + '-' + ERROR_MESSAGE(),

                                                                                @ErrorLogFunction = ERROR_PROCEDURE(),

                                                                                @ErrorLogNumber = ERROR_NUMBER()

                                                                EXECUTE dbo.[USP_TP_ErrorLog_Insert]

                                                                                @ProcessID,

                                                                                @ErrorLogNumber,

                                                                                @ErrorLogLineNumber,

                                                                                @ErrorLogMessage,

                                                                                @ErrorLogFunction,

                                                                                'USP'

                                                END                                        

                                               

                 END CATCH                            

                                                               

                SET NOCOUNT OFF;

                               

END

    

                               

GO

-- You can test the query passing different parameters as shown below.

-- 1^st Page Resultset

EXEC [USP_TP_Table_Query_Record_Get_ByPage]

1,             -- @ProcessID

4,             -- @PageSize

1,             -- @PageIndex

'SELECT * FROM dbo.ErrorLog WHERE ErrorLogDate < GETDATE()',  -- @Query

'99',          -- @SearchValue

'ErrorLogMessage, ErrorLogNumber',             -- @SearchColumns

'*',            -- @SelectColumns  here you can specify only coulumns to be selected like ErrorLogMessage, ErrorLogFunction instead of '*'

'ErrorLogMessage', -- @SortColumn

'ASC',                       -- @SortBy 

'ErrorLog'   -- @SortTable

-- 2nd Page Resultset

EXEC [USP_TP_Table_Query_Record_Get_ByPage]

1,             -- @ProcessID

4,             -- @PageSize

1,             -- @PageIndex

'SELECT * FROM dbo.ErrorLog WHERE ErrorLogDate < GETDATE()',  -- @Query

'99',          -- @SearchValue

'ErrorLogMessage, ErrorLogNumber',             -- @SearchColumns

'*',            -- @SelectColumns  here you can specify only coulumns to be selected like ErrorLogMessage, ErrorLogFunction instead of '*'

'ErrorLogMessage', -- @SortColumn

'ASC',                       -- @SortBy 

'ErrorLog'   -- @SortTable

EXAMPLE: 2

How to convert amount to word in SQL?

SQL function returns amount to word.

-- ======================================================

/*

AUTHOR                   : Govind

CREATE DATE  : 16-Oct-2014

DESCRIPTION  : To convert amount to word.

SET STATISTICS TIME ON

SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL ,1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](0, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.49, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.49, @@NESTLEVEL, 2, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.49, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.49, @@NESTLEVEL, 2, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.50, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](0.50, @@NESTLEVEL, 2, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 2, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](10012.44, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](10012.44, @@NESTLEVEL, 2, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-12.44, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL, 2, 2)

SET STATISTICS TIME OFF

*/

-- ======================================================

CREATE FUNCTION [dbo].[UDF_PT_ConvertAmountToWord]

(

@Money AS MONEY,

@NestLevel AS INT     = 0,    -- Always pass @@NESTLEVEL instead of any value

@Type AS SMALLINT     = 1,    -- 1 Cents eg. [Twelve Dollars 44/100 Cents], 2-Dollars eg. [Twelve and 44/100 Dollars]

@FirstTime AS TINYINT = 1     -- By default 1 when calling this function.

)

RETURNS VARCHAR(1024)

AS

BEGIN

                DECLARE @Number AS BIGINT

                DECLARE @MinusFlag AS BIT

                DECLARE @ZeroWord AS VARCHAR(10)=''

               

                -- Specify the currency name and coin name.

                DECLARE @CurrencyName AS VARCHAR(10)='Dollars'

                DECLARE @CoinName AS VARCHAR(10)='Cents'

               

               

                IF @Money = 0 AND ISNULL(@FirstTime,1) = 1

                BEGIN

                                RETURN 'Zero'

                END

               

                IF (ISNULL(@FirstTime,1) = 1 AND ((ROUND(@Money,0) = 0) OR (FLOOR(@Money) = 0) OR (CEILING(@Money) = 0)))

                BEGIN

                                SET @ZeroWord = 'Zero'

                END

               

                SET @FirstTime = 2

                IF @Money < 0

                BEGIN

                                SET @Money = -1 * @Money

                                SET @MinusFlag = 1

                END

                SET @Number = FLOOR(@Money)

                DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))

                DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))

                INSERT INTO @Below20 (Word) VALUES ('Zero')

                INSERT INTO @Below20 (Word) VALUES ('One')

                INSERT INTO @Below20 (Word) VALUES ('Two')

                INSERT INTO @Below20 (Word) VALUES ('Three')

                INSERT INTO @Below20 (Word) VALUES ('Four')

                INSERT INTO @Below20 (Word) VALUES ('Five')

                INSERT INTO @Below20 (Word) VALUES ('Six')

                INSERT INTO @Below20 (Word) VALUES ('Seven')

                INSERT INTO @Below20 (Word) VALUES ('Eight')

                INSERT INTO @Below20 (Word) VALUES ('Nine')

                INSERT INTO @Below20 (Word) VALUES ('Ten')

                INSERT INTO @Below20 (Word) VALUES ('Eleven')

                INSERT INTO @Below20 (Word) VALUES ('Twelve')

                INSERT INTO @Below20 (Word) VALUES ('Thirteen')

                INSERT INTO @Below20 (Word) VALUES ('Fourteen')

                INSERT INTO @Below20 (Word) VALUES ('Fifteen')

                INSERT INTO @Below20 (Word) VALUES ('Sixteen')

                INSERT INTO @Below20 (Word) VALUES ('Seventeen')

                INSERT INTO @Below20 (Word) VALUES ('Eighteen')

                INSERT INTO @Below20 (Word) VALUES ('Nineteen')

                INSERT INTO @Below100 (Word) VALUES ('Twenty')

                INSERT INTO @Below100 (Word) VALUES ('Thirty')

                INSERT INTO @Below100 (Word) VALUES ('Forty')

                INSERT INTO @Below100 (Word) VALUES ('Fifty')

                INSERT INTO @Below100 (Word) VALUES ('Sixty')

                INSERT INTO @Below100 (Word) VALUES ('Seventy')

                INSERT INTO @Below100 (Word) VALUES ('Eighty')

                INSERT INTO @Below100 (Word) VALUES ('Ninety')

                DECLARE @English VARCHAR(1024)

                SET @English = ''

                (

                SELECT @English =

                CASE

                                WHEN @Number = 0 THEN ''

                                WHEN @Number BETWEEN 1 AND 19

                                THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

                               

                                WHEN @Number BETWEEN 20 AND 99

                                THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ + ' '+ -- '-' +

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 10, @NestLevel, @Type ,2)

                                WHEN @Number BETWEEN 100 AND 999

                                THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 100, @NestLevel, @Type, 2))+' Hundred '+

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 100, @NestLevel, @Type, 2)

                                WHEN @Number BETWEEN 1000 AND 999999

                                THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000, @NestLevel, @Type, 2))+' Thousand '+

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000, @NestLevel, @Type, 2)

                                WHEN @Number BETWEEN 1000000 AND 999999999

                                THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000, @NestLevel, @Type, 2))+' Million '+

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000, @NestLevel, @Type, 2)

                                WHEN @Number BETWEEN 1000000000 AND 999999999999

                                THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000000, @NestLevel, @Type, 2))+' Billion '+

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000000, @NestLevel, @Type, 2)

                                WHEN @Number BETWEEN 1000000000000 AND 99999999999999

                                THEN (dbo.[UDF_PT_ConvertAmountToWord]( @Number / 1000000000000, @NestLevel, @Type, 2))+' Trillion '+

                                dbo.[UDF_PT_ConvertAmountToWord]( @Number % 1000000000000, @NestLevel, @Type, 2)

                ELSE

                                'INVALID INPUT'

                END

                )

                SELECT @English = RTRIM(@English)

                SELECT @English = RTRIM(LEFT(@English,LEN(@English)-1)) WHERE RIGHT(@English,1)='-'

               

                IF (@@NestLevel - @NestLevel) = 1 AND @English!='INVALID INPUT'

                BEGIN

                               

                                IF @Type = 1

                                BEGIN

                                                SELECT @English = @ZeroWord + @English+' ' + @CurrencyName + ' and '

                                                SELECT @English = @English+ CONVERT(VARCHAR,CONVERT(INT,100*(@Money - @Number))) +' ' + @CoinName 

                                END        

                                ELSE

                                BEGIN

                                                IF @ZeroWord = ''

                                                                SELECT @English = @English+' and '

                                                               

                                                SELECT @English = @English+ CONVERT(VARCHAR,CONVERT(INT,100*(@Money - @Number))) +'/100 ' + @CurrencyName

                                END                                        

                                IF (@MinusFlag = 1)

                                BEGIN

                                                SET @English = '( ' + @English + ' )'

                                END

                               

                END

                RETURN (@English)

END

GO

-- You can test the function passing differnet parameters as shown below.

SELECT [dbo].[UDF_PT_ConvertAmountToWord](-0.50, @@NESTLEVEL, 1, 1)

SELECT [dbo].[UDF_PT_ConvertAmountToWord](12345678901234.78, @@NESTLEVEL, 1, 2)

GO