Customized paging options using SQL server for Paging, Sorting and Searching ... Very fast and robust solution

****************************** THE SP

ALTER PROCEDURE [dbo].[caw_XYZs_GridView]
@startrec int
, @endrec int
, @statusfilterid int
, @filter_XYZname varchar(50)
, @filter_skucount varchar(50)
, @sortcolumn varchar(2)
, @sortdir varchar(4)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 12-14-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN TRY
DECLARE @TotalRows numeric(16, 2)

IF @filter_XYZname IS NULL
AND @filter_skucount IS NULL
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
ELSE
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))

SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount +  '%'))
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END

SELECT TotalRows
, TotalPages
FROM dbo.fun_PagingAndRows(@TotalRows)
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END

******************************* THE FUNCTION 

ALTER FUNCTION [dbo].[fun_PagingAndRows]

(

@totalrows int

)

RETURNS @rows TABLE

(

TotalRows int

, TotalPages int

)

AS

BEGIN

/*------------------------------------------------------------------------------------------------------------------------------------------------------

Modified Date: 10-01-2015

------------------------------------------------------------------------------------------------------------------------------------------------------*/

INSERT INTO @rows

(

TotalRows

, TotalPages

)

SELECT ISNULL(CONVERT(int, @TotalRows), 0) AS TotalRows

, CASE WHEN (CEILING(ISNULL(@TotalRows, 0) / 10.0)) < 1 THEN 1

ELSE (CEILING(ISNULL(@TotalRows, 0) / 10.0))

END AS TotalPages

RETURN

END

Understand the Universal time-zone

Type DateTime

All date and time data is expressed as calendar dates (year, month, day) and a 24-hour day. Day and time data is always expressed in a combined format. No truncated formats are allowed. Dates are based on the Gregorian calendar. All date and time data is expressed numerically, leading zeros are preserved. Time can be expressed down to millisecond resolution, if necessary.

The basic type DateTime string pattern is yyyy-MM-ddTHH:mm:ss.sssZhhmm or date-element 'T' time-element 'Z' [zone-offset]. The character 'T' is arequired delimiter between the date and time sections. Times are expressed in the extended format: the ':' is used as a delimiter between hours, minutes and seconds. Fractional seconds are expressed with a decimal mark ('.' or ',') when needed.

 

 yyyy-MM-ddTHH:mm:ss.sssZ±hhmm

• date-element = yyyy [year-element]
• month-element = '-' MM [months-element]
• day-element = '-' dd [days-element]
• date-time delimiter = 'T'
• time-element = HH [hours-element]
• minute-element = ':' mm [minutes-element]
• second-element = ':' ss [seconds-element]
• fraction = ('.' | ',') digit+ [fraction]
• zone-offset = 'Z' | (('+' | '-') HH [mm])

 

Type conflicts

All use of date and time data in Amazon MWS should be expressed as type DateTime. However, there are a few parameters that express date or time as a simple string. Check the parameter type definition to be sure of which type to use. Using the wrong type will generate an error.

Timezone considerations

Time data is always based on a 24-hour timekeeping system and Coordinated Universal Time (UTC). UTC is also known as Zulu (Z) time. The character 'Z' is expected as the UTC designator in the date time string.

Local timezones can be used, but must be expressed using a UTC offset. Offsets can be expressed as ±hh, where +hh is the local time (in hours) ahead of UTC and -hh is the local time behind UTC.

For example: Z+02 means UTC plus two hours ahead, and Z-04 means UTC minus four hours behind.

If no offset is specified, the API will assume UTC time, even if the 'Z' is missing.

While UTC can be expressed as Z+00, it is not required. The use of a terminal Z and no offset is permitted.

Time intervals

While time intervals and durations are allowed under ISO 8601, they are not currently used in Amazon MWS.

Example:

  2016-03-16T14:32:16.50Z-07

This example is read as the 16th day of March in 2016 at 14hrs 32min and 16.50 seconds UTC. The local time is 7 hours behind UTC.

 

SQL Server Cross ref. Tables used in another database store procedures

----- Cross ref. Table used in another database



SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,

referenced_schema_name, referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referenced_database_name ='XYZ' --- Target(Ref) DB

AND is_ambiguous = 0;

SQL Get List of Tables and SPs and Views Used/NotUsed of Database

 -- used XYZ DB views into PQR DB SPs


SELECT DISTINCT




--SP_Name = O.name,


Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'V'



　

---- count of used sps in db



WITH T1




as


(


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

)

,



T2

as

(

SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

)

select *,COUNT (T1.SPECIFIC_NAME) OVER (PARTITION BY T1.SPECIFIC_NAME) from T1

LEFT JOIN T2 ON T1.SPECIFIC_NAME = T2.SPECIFIC_NAME





---- not used sp by execution time


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

And SPECIFIC_NAME not in




(


SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'




)



----used sp by execution time

SELECT o.name,

ps.last_execution_time

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

ORDER BY

ps.last_execution_time DESC




---- get name of sps


select *

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'



　

-- not used tables


SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='PQR'

And TABLE_NAME not in

(SELECT DISTINCT

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P')




-- used tables


SELECT DISTINCT

SP_Name = O.name,

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'U'