Declare SQL Cursor (Mssql)

How to use Cursor in MS Sql?

Cursors allow you to fetch a set of data, loop through each record, and modify the values as necessary;

sampleCursor includes all rows but only ID column in the table. Do you want to see all columns you must use

1
  SELECT * FROM TableA syntax.

The following example shows how cursors can be used in sql server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CursorExampleSP] AS 
DECLARE @Id BIGINT
 
DECLARE sampleCursor CURSOR FOR 
      SELECT K.Id FROM TableA K WHERE ....;
OPEN sampleCursor
FETCH NEXT FROM sampleCursor INTO @Id
WHILE @@FETCH_STATUS <> -1
BEGIN
 
UPDATE TableB
   SET 
      ...
 
FETCH NEXT FROM sampleCursor INTO @Id
 
END
CLOSE sampleCursor
DEALLOCATE sampleCursor


Views (11535)

Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

SoftCodeArticle was formed to help developers solve common software issues.