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.

-- 1st 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



2 comments:

  1. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql dba training
    sql server dba online training

    ReplyDelete
  2. https://www.programingtutorial.com/

    ReplyDelete