SQL Stored Procedure - Understanding SQL Statement

Question

New to stored procedures. Can anyone explain the following SQL sample which appears at the start of a stored procedure?

Begin/End - Encloses a series of SQL statements so that a group of SQL statements can be executed

SET NOCOUNT ON - the count (indicating the number of rows affected by a SQL statement) is not returned.

DECLARE - setting local variables

While - loops round

With - unsure

Update batch - unsure

SET @Rowcount = @@ROWCOUNT; - unsure

    BEGIN
             SET NOCOUNT ON;

--UPDATE, done in batches to minimise locking
             DECLARE @Batch INT= 100;
             DECLARE @Rowcount INT= @Batch;
             WHILE @Rowcount > 0
                 BEGIN
                     WITH t
                          AS (
                          SELECT [OrganisationID],
                                 [PropertyID],
                                 [QuestionID],
                                 [BaseAnsweredQuestionID]
                          FROM dbo.Unioned_Table
                          WHERE organisationid = 1),
                          s
                          AS (
                          SELECT [OrganisationID],
                                 [PropertyID],
                                 [QuestionID],
                                 [BaseAnsweredQuestionID]
                          FROM dbo.table
                          WHERE organisationid = 1),
                          batch
                          AS (
                          SELECT TOP (@Batch) T.*,
                                              s.BaseAnsweredQuestionID NewBaseAnsweredQuestionID
                          FROM T
                               INNER JOIN s ON t.organisationid = s.organisationid
                                               AND t.PropertyID = s.PropertyID
                                               AND t.QuestionID = s.QuestionID
                          WHERE t.BaseAnsweredQuestionID <> s.BaseAnsweredQuestionID)
                          UPDATE batch
                            SET
                                BaseAnsweredQuestionID = NewBaseAnsweredQuestionID
                     SET @Rowcount = @@ROWCOUNT;
                 END;

Neville Kuyt · Accepted Answer · 2019-06-20 10:37:48Z

3

The clue is in the comment --UPDATE, done in batches to minimise locking.

The intent is to update dbo.table's column BaseAnsweredQuestionID with the equivalent column from dbo.Unioned_Table, in batches of 100. The comment suggests the batching logic is necessary to prevent locking.

In detail:

DECLARE @Batch INT= 100; sets the batch size.

DECLARE @Rowcount INT= @Batch; initializes the loop. WHILE @Rowcount > 0 starts the loop. @Rowcount will become zero when the update statement affects no rows (see below).

with a as () is a common table expression (commonly abbreviated to CTE) - it creates a temporary result set which you can effectively treat as a table. The next few queries define CTEs t, s and batch.

CTE batch contains just 100 rows by using the SELECT TOP (@Batch) term - it selects a random 100 rows from the two other CTEs.

The next statement:

UPDATE batch
   SET BaseAnsweredQuestionID = NewBaseAnsweredQuestionID
SET @Rowcount = @@ROWCOUNT

updates the 100 rows in the batch CTE (which in turn is a join on two other CTEs), and populates the loop variable @Rowcount with the number of rows affected by the update statement (@@ROWCOUNT). If there are no matching rows, @@ROWCOUNT becomes zero, and thus the loop ends.

answered Jun 20, 2019 at 10:37

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

A SQL Server table will see a lock escalation (from using individual row-level locks to an exclusive table lock) when a single transaction uses more than 5000* locks.

Collectives™ on Stack Overflow

SQL Stored Procedure - Understanding SQL Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related