Problem using STUFF() with UPDATE ...

[stevesaved]

Greetings,

Can anyone help?? ANY help would be GREATLY appreciated.

I have a function that uses stuff() to place characters at specific positions within a column. It works when run independently but not as part of an UPDATE

1. I have a table called db.NewArticleCode with the following row:

ArticleCode: RB9212.
strNewArticleCode: RB9212.

2. I have a table called dbption with the following rows

ArticleCode: RB9212.
nStartPos: 8
nLength: 2

ArticleCode: RB9212.
nStartPos: 11
nLength: 2

3. I want to add x's to dbo.NewArticleCode.strNewArticleCode in the positions specified in dbption, dbption.nStartPos tells me where the x's should begin, and dbption.nLength tells me how many x's are required.

From the rows in dbption, dbo.NewArticleCode.strNewArticleCode should end up as: RB9212.XX XX

when I run the following query I end up with: RB9212. XX.

1. It works if I call the function twice manually, using variables instead of tables

2. Works with the UPDATE query the first time I run it in SQL server

3. Stops working correctly after I refresh the dbo.NewArticleCode table. It only seems to apply the last update and not the first.

Can anyone tell me why? ANY HELP WILL BE GREATLY APPRECIATED!

My query is:

UPDATE dbo.NewArticleCode
SET dbo.NewArticleCode.strNewArticleCode = dbo.fnInsertPlaceHolder( dbo.NewArticleCode.strNewArticleCode, [dbo].[option].nStartPos, [dbo].[Option].nLength )

FROM dbo.Article INNER JOIN [dbo].[Option] ON [dbo].[Option].lOptionSetID = dbo.Article.lOptionSetID INNER JOIN dbo.NewArticleCode ON dbo.NewArticleCode.lArticleID = dbo.Article.lArticleID
WHERE [dbo].[Option].nStartPos > 0


fnInsertPlaceHolder contains:

FUNCTION [dbo].[fnInsertPlaceHolder]
(@strArticleCode varchar(100), @iStartPos int, @iLength int)
RETURNS varchar(100)
as
BEGIN

declare @strNewArticleCode varchar(100)
declare @iCurrentLength int

/* set up variable big enough to hold completed article code */
set @strNewArticleCode = replicate( ' ', 100 )

/* get current length of article code & options */
set @iCurrentLength = len( @strArticleCode )

/* put current article code & options into new field */
set @strNewArticleCode = stuff( @strNewArticleCode, 1, @iCurrentLength, @strArticleCode )

/* add the current option to the article code */
set @strNewArticleCode = stuff( @StrNewArticleCode, @iStartPos, @iLength, replicate( 'X', @iLength ) )

return @strNewArticleCode
END

 

[zhavic]

It only seems to apply the last update and not the first.

That is it, because the all changes to rows are affected only after end of statement, not within its execution.

You can use cursor to select all options for each row,
go throught it and get your @strNewArticleCode,
but it may be slow

Or, if you know the maximum count of options that may be used,
and this count is small ( for example 5 ), you can LEFT JOIN
each of options to row and pass all options to your function together, and aplly only these, that are not null, but you must have one column in [options] table, which will indicate
order of option


Something like:
Let say, that for some row, [option] table has this options:

ArticleCode: RB9212.
nStartPos: 8
nLength: 2
Order: 1

ArticleCode: RB9212.
nStartPos: 11
nLength: 2
Order: 2

Use query similar to this:

UPDATE dbo.NewArticleCode
    SET dbo.NewArticleCode.strNewArticleCode = 
			dbo.fnInsertPlaceHolder( dbo.NewArticleCode.strNewArticleCode, 
									option1.nStartPos,
								    option1.nLength,
									option2.nStartPos,
								    option2.nLength,
									option3.nStartPos,
								    option3.nLength,
									option4.nStartPos,
								    option4.nLength,
									option5.nStartPos,
								    option5.nLength
								  )
FROM dbo.Article 
	LEFT JOIN [dbo].[Option] option1 ON option1.lOptionSetID = dbo.Article.lOptionSetID AND option1.[Order] = 1
	LEFT JOIN [dbo].[Option] option2 ON option2.lOptionSetID = dbo.Article.lOptionSetID AND option1.[Order] = 2
	LEFT JOIN [dbo].[Option] option3 ON option3.lOptionSetID = dbo.Article.lOptionSetID AND option1.[Order] = 3
	LEFT JOIN [dbo].[Option] option4 ON option4.lOptionSetID = dbo.Article.lOptionSetID AND option1.[Order] = 4
	LEFT JOIN [dbo].[Option] option5 ON option5.lOptionSetID = dbo.Article.lOptionSetID AND option1.[Order] = 5
	INNER JOIN dbo.NewArticleCode ON dbo.NewArticleCode.lArticleID = dbo.Article.lArticleID
WHERE option1.nStartPos > 0   -- with this condition there is not needed INNER JOIN for the 'option1'

Change your function to accept all the parameters and within it use only this parameters, that are not NULL and has nStartPos > 0, offcourse, in order that they appear in parameter list

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[stevesaved]

Thanks for your help,

I decided to use the CURSOR because speed was not an issue.

Regards.