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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s