Avoid use of sp_xml_removedocument and Cursor in Stored procedure

CREATE PROCEDURE Avoid_sp_xml_preparedocument_And_Cursor

@xml xml,

AS

DECLARE @tmpValues_tbl

TABLE ([RowNum] [int] identity(1,1),[ID] int,[Index1] int ,[Index2] int,[Remark] varchar(max) null,)

INSERT into @tmpValues_tbl ([ParamID],[Index1ID],[Index2ID],[ParamValueRemark])

SELECT  [ID],[Index1],[Index2],[Remark]

FROM(SELECT

ID = T.Item.query(‘ID’).value(‘.’,’INT’)

,Index1 = T.Item.query(‘Index1’).value(‘.’,’int’)

,Index2 = T.Item.query(‘Index2’).value(‘.’,’int’)

,Remark = CASE WHEN T.Item.exist(‘Remark’)=1 THEN T.Item.query(‘Remark’).value(‘.’,’nvarchar(max)’) ELSE NULL END

FROM @xml.nodes(‘NewDataSet/Table1’) AS T(Item))

SELECT * FROM @tmpValues_tbl

DECLARE @ID INT

DECLARE @Index1 INT

DECLARE @Index2 INT

DECLARE @Remark VARCHAR(8000)

DECLARE @RowCnt1 INT

set @RowCnt1 = 1

DECLARE @MaxRows1 INT

SELECT @MaxRows1=count(*) FROM @tmpValues_tbl

— loop through @ParamAliasName table AND alter columns to add one

 WHILE @RowCnt1 <= @MaxRows1

BEGIN

SELECT  @ID= [ID] ,@Index1= [Index1] ,@Index2= [Index2] ,@Remark= [Remark] ,FROM @prjParamValues_tbl where rownum =@RowCnt1

— use variable here you want to

SET @RowCnt1 = @RowCnt1 + 1 — increase count by 1

END –while

Advertisements

Avoid Cursor in sql serve 2005

There is no need to explain much that using cursor in sql
script is an overhead . I am not saying that we can avoid cursors
completely but we can replace it with temp table in almost all
the places . Here is an small example to explain it ::::

DECLARE @RowCnt INT
DECLARE @MaxRows INT
DECLARE @ParamAliasName NVARCHAR(255)
DECLARE @columnName NVARCHAR(255)

DECLARE @ParamAliasName table
(
rownum INT IDENTITY (1, 1) Primary key NOT NULL ,
AliasKey NVARCHAR(255),

)

INSERT INTO @ParamAliasName (AliasKey)
SELECT pol.AliasKey FROM sometbl pol
–INNER JOIN wbsparam_tbl w on w.ParamID = pol.ParamID
WHERE

SELECT @MaxRows=count(*) FROM @ParamAliasName

while @RowCnt <= @MaxRows
begin
— do your cursor operation in this loop
SELECT @rc=0
SELECT @columnName=AliasKey, FROM @ParamAliasName
WHERE rownum = @RowCnt
SET @RowCnt = @RowCnt + 1 — increase count by 1
end