TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Austin Muts
1.4k
327
124.8k
Using a cursor to loop through data in SQL
Sep 25 2019 4:05 AM
My @SQL is not executing because of my synatax,any help will be appreciated
DECLARE
@SQL NVARCHAR(
MAX
) =
''
;
BEGIN
IF OBJECT_ID(
'tempdb..#Config'
)
IS
NOT
NULL
DROP
TABLE
#Config;
CREATE
TABLE
#Config
(
[TempID]
INT
IDENTITY(1, 1),
[ID] NVARCHAR(36),
[DocID] NVARCHAR(36),
[FieldNo]
INT
,
[FieldName] NVARCHAR(450),
[DisplayName] NVARCHAR(450),
[Description] NVARCHAR(450),
[DefaultValue] NVARCHAR(450),
[DisplayFormat] NVARCHAR(450),
[MaxLength]
INT
,
[MinLength]
INT
,
[DataType]
SMALLINT
,
[ColumnName] NVARCHAR(450),
[BarcodeNo]
INT
,
[DataBehaviour]
SMALLINT
,
[ListID] NVARCHAR(36),
[MapToString]
BIT
,
[ScanIndex]
INT
,
[ScanRequired]
INT
,
[ValidationID] NVARCHAR(450),
[RegexValue] NVARCHAR(450),
[ValidationTrigger]
SMALLINT
,
[DecimalPrecision]
INT
,
[DBCanNull]
BIT
,
[IsPrimary]
BIT
,
[IsAuditable]
BIT
,
[DBSchemaDefault]
BIT
,
[DBSchemaDefaultValue] NVARCHAR(450),
[Updated] DATETIME,
[UpdatedBy] NVARCHAR(450) ,
[Created] DATETIME,
[CreatedBy] NVARCHAR(450),
[SourceID] NVARCHAR(450)
)
INSERT
INTO
#Config
VALUES
(NEWID(),
'b5f3cf6e-190b-4132-b90e-d320414cfaee'
, 1,
'ApplicationID'
,
'Application_ID'
,
'Idx1'
, N
''
, N
''
, 30, 0, 0, N
'ApplicationID'
, 0, 0, N
''
, 0, 0, 0, N
''
,
NULL
, 4, 0, 1, 0, 0, 0,
NULL
,
CAST
(N
'2019-09-23 10:26:35.943'
AS
DateTime), N
'1aa591f0-a2c1-41b3-815b-68e0f5ef9478'
,
CAST
(
'2019-09-23 10:19:25.197'
AS
DateTime),
'1aa591f0-a2c1-41b3-815b-68e0f5ef9478'
,
NULL
)
SELECT
*
FROM
#Config
DECLARE
@TempID
INT
= 0;
DECLARE
@ID NVARCHAR(36) =
''
;
DECLARE
@DocID NVARCHAR(36) =
''
;
DECLARE
@FieldNo
INT
= 0;
DECLARE
@FieldName NVARCHAR(450) =
''
;
DECLARE
@DisplayName NVARCHAR(450) =
''
;
DECLARE
@Description NVARCHAR(450) =
''
;
DECLARE
@DefaultValue NVARCHAR(450) =
''
;
DECLARE
@DisplayFormat NVARCHAR(450) =
''
;
DECLARE
@MaxLength
INT
= 0;
DECLARE
@MinLength
INT
= 0;
DECLARE
@DataType
SMALLINT
= 0;
DECLARE
@ColumnName NVARCHAR(450) =
''
;
DECLARE
@BarcodeNo
INT
= 0;
DECLARE
@DataBehaviour
SMALLINT
= 0;
DECLARE
@ListID NVARCHAR(36) =
''
;
DECLARE
@MapToString
BIT
=
''
;
DECLARE
@ScanIndex
INT
= 0;
DECLARE
@ScanRequired
INT
= 0;
DECLARE
@ValidationID NVARCHAR(450) =
''
;
DECLARE
@RegexValue NVARCHAR(450) =
''
;
DECLARE
@ValidationTrigger
SMALLINT
= 0;
DECLARE
@DecimalPrecision
INT
= 0;
DECLARE
@DBCanNull
BIT
=
''
;
DECLARE
@IsPrimary
BIT
=
''
;
DECLARE
@IsAuditable
BIT
=
''
;
DECLARE
@DBSchemaDefault
BIT
=
''
;
DECLARE
@DBSchemaDefaultValue
BIT
=
''
;
DECLARE
@Updated DATETIME =
''
;
DECLARE
@UpdatedBy NVARCHAR(450) =
''
;
DECLARE
@Created DATETIME =
''
;
DECLARE
@CreatedBy NVARCHAR(450) =
''
;
DECLARE
@SourceID NVARCHAR(450) =
''
;
DECLARE
cursor_document
CURSOR
FOR
SELECT
[TempID] ,
[ID] ,
[DocID] ,
[FieldNo] ,
[FieldName] ,
[DisplayName] ,
[Description] ,
[DefaultValue] ,
[DisplayFormat] ,
[MaxLength] ,
[MinLength] ,
[DataType] ,
[ColumnName] ,
[BarcodeNo] ,
[DataBehaviour] ,
[ListID] ,
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID] ,
[RegexValue] ,
[ValidationTrigger] ,
[DecimalPrecision] ,
[DBCanNull] ,
[IsPrimary] ,
[IsAuditable] ,
[DBSchemaDefault] ,
[DBSchemaDefaultValue] ,
[Updated] ,
[UpdatedBy] ,
[Created] ,
[CreatedBy] ,
[SourceID]
FROM
#Config;
OPEN
cursor_document;
FETCH
NEXT
FROM
cursor_document
INTO
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'CREATING DOCUMENT :'
+ @DocID +
' FROM : '
+
CAST
(@DocID
AS
VARCHAR
);
SELECT
@SQL = N'
INSERT
INTO
[dbo].[SysDoc]
([ID],
[DocID],
[FieldNo],
[FieldName],
[DisplayName],
[Description],
[DefaultValue],
[DisplayFormat],
[MaxLength],
[MinLength],
[DataType],
[ColumnName],
[BarcodeNo],
[DataBehaviour],
[ListID],
[MapToString],
[ScanIndex],
[ScanRequired],
[ValidationID],
[RegexValue],
[ValidationTrigger],
[DecimalPrecision],
[DBCanNull],
[IsPrimary],
[IsAuditable],
[DBSchemaDefault],
[DBSchemaDefaultValue],
[Updated],
[UpdatedBy],
[Created],
[CreatedBy],
[SourceID]
SELECT
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID '
PRINT @SQL
EXEC
@SQL
FETCH
NEXT
FROM
cursor_document
INTO
@TempID,
@ID,
@DocID ,
@FieldNo ,
@FieldName ,
@DisplayName,
@Description ,
@DefaultValue,
@DisplayFormat,
@MaxLength ,
@MinLength,
@DataType,
@ColumnName,
@BarcodeNo ,
@DataBehaviour ,
@ListID ,
@MapToString,
@ScanIndex,
@ScanRequired,
@ValidationID,
@RegexValue ,
@ValidationTrigger,
@DecimalPrecision ,
@DBCanNull ,
@IsPrimary,
@IsAuditable,
@DBSchemaDefault ,
@DBSchemaDefaultValue,
@Updated ,
@UpdatedBy,
@Created ,
@CreatedBy,
@SourceID
END
CLOSE
cursor_document;
DEALLOCATE
cursor_document;
END
Reply
Answers (
6
)
Problem with syntax in Dynamic Sql statement
Timeout error in Execute SQL action in Nintex Workflow in