Category: SQL Server Page 3 of 4

This is very important for the most web developers. This stored procedure accept parameter of page index and page size. By using this paging, you don’t need to store everything in your viewstate which in the end will cause performance suffer. This will load up the necessary result based on the page index and page size and it uses row number to put the number in each row.

Datagrid needs to use custom paging = true as well as virtualitem count to produce the number for paging.

Datagrid in ASPX Page

Code Behind

//function to do the paging itself
  private void LoadContent(int pageIndex)
    {
        _presenter.GetContentSubmissionSelect(pendingStatusID, pageIndex, PAGE_SIZE);

        if (GetContentSubmissionSelect.Rows.Count > 0)
        {
            lblNoContent.Visible = false;

            dgTaskList.Visible = true;
            dgTaskList.AllowCustomPaging = true;
            dgTaskList.AllowPaging = true;
            dgTaskList.PageSize = PAGE_SIZE;
            _presenter.GetContentSubmissionCount(pendingStatusID);

            dgTaskList.VirtualItemCount = Convert.ToInt32(GetContentSubmissionCount.Rows[0][0]);
            dgTaskList.DataSource = GetContentSubmissionSelect;
            dgTaskList.CurrentPageIndex = pageIndex;
            dgTaskList.DataBind();
        }
        else
        {
            dgTaskList.Visible = false;
            lblNoContent.Visible = true;
        }
    }

//handle the page index changed
    protected void dgTaskList_PageIndexChanged(object sender, DataGridPageChangedEventArgs e)
    {
        LoadContent(e.NewPageIndex);
    }

SQL Stored Procedure
We need to have two query where one to get the number of records and the other one is to get the actual result it self
To get the query/actual result

CREATE PROCEDURE uspContentSubmissionSelect
(
	@statusID int = NULL,
	@PageIndex int,
	@PageSize int
)
AS
	SET NOCOUNT ON;
SELECT
	o.Id, o.ContentSubmission_id, o.AppointmentId,
	o.Title, o.StatusName
FROM
	(
		SELECT
			r.ID, r.contentSubmission_id, r.AppointmentId,
			r.Title, s.StatusName,
			ROW_NUMBER() OVER (ORDER BY r.ID DESC) as RowNumber
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.Id = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))
	) o
WHERE
	o.RowNumber BETWEEN (@PageIndex * @PageSize + 1) AND ((@PageIndex * @PageSize) + @PageSize)
ORDER BY RowNumber
GO

To get the record count

CREATE PROCEDURE [dbo].[uspContentSubmissionCount]
(
	@statusID int = NULL
)
AS
	SET NOCOUNT ON;

		SELECT
			COUNT(*) AS TotalRow
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.ID = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))

I’ve got a question this morning about how to find a particular query in a huge list of stored procedure. It’s not that hard, what you need to do is to use SYS object and sys.procedures basically contains all the stored procedure under the active database that you are using. This is the query that you can use to find a keyword product in any stored procedure

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%product%'
    AND ROUTINE_TYPE='PROCEDURE'

And the query below is used to find the stored procedure name that match with what you are looking for

SELECT *
FROM sys.procedures where OBJECT_DEFINITION(object_id) LIKE '%product%'

UPDATE:
How to find all the triggers on your Database

SELECT tbl1.[name] TableName, tbl0.[name] TriggerName,
CASE
WHEN tbl1.deltrig = tbl0.id  THEN 'OnDelete'
WHEN tbl1.instrig = tbl0.id THEN 'OnInsert'
WHEN tbl1.updtrig = tbl0.id THEN 'OnUpdate'
END 'Operation Type', tbl1.*,tbl0.*
FROM sysobjects tbl0 JOIN sysobjects tbl1 ON tbl0.parent_obj = tbl1.[id] WHERE tbl0.xtype='TR'

I tried to create an average in sql server by dividing two figures. Once I got the error of “Divide by zero error occured”, this is caused by dividing the figure by null value, to handle the error we can use “NULLIF”

DECLARE @ThisMonthAvg decimal(8,2)
SET @ThisMonthAvg = @ThisMonthPlayers / NULLIF(@ThisMonthEvents, 0)

DECLARE @LastMonthAvg decimal(8,2)
SET @LastMonthAvg = @LastMonthPlayers / NULLIF(@LastMonthEvents, 0)

DECLARE @ThisYearAvg decimal(8,2)
SET @ThisYearAvg = @ThisYearPlayers / NULLIF(@ThisYearEvents, 0)

DECLARE @LastYearAvg decimal(8,2)
SET @LastYearAvg = @LastYearPlayers / NULLIF(@LastYearEvents, 0)