Problem with updating table using parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with updating table using parameters

Author

Topic

meustace
Starting Member

2 Posts

Posted - 2009-05-20 : 12:33:40

I have created a table and populated it with some data. I have a procedure that creates a temporary table with related data. The relationship is on a field called SysID. I want to loop through the temporary table and concatenate the related data into one of the fields in the static table. I have the following snippet that does that.


declare @n int, @name varchar(60), @SysID int

select @n=max(i) from #tmpHRSecurity

while @n>0
BEGIN
	--select [Name], SysID from #tmpHRSecurity where i=@n
	select @name=[Name], @SysID=SysID from #tmpHRSecurity where i=@n
	update tblHRFilePlan set security = security + @name + ' - ' where SysID=@SysID
	select @n = @n-1
END

The problem is that although there is data in the temporary table and it matches on sysid to the static table, the update actually does nothing, the security column remains NULL. There are 10,000 lines of data in the temporary table and if I run this in Query Analyser, I get 10,000 repeats of "(1 row(s) affected)". If I uncomment the select immediately below the BEGIN statement, I get 10,000 correct results shown and if I cut and paste data from the results into the update statement and run it manually for a single line, that works fine. i is an indentity field. Any ideas on what may be wrong? I've had very similar syntax running fine before and I don't know what's gone wrong here!

TIA,
Matt.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:20:30

try this


declare @n int, @name varchar(60), @SysID int

select @n=max(i) from #tmpHRSecurity

while @n>0
BEGIN
	--select [Name], SysID from #tmpHRSecurity where i=@n
	select @name=[Name], @SysID=SysID from #tmpHRSecurity where i=@n
	update tblHRFilePlan set security = coalesce(security,'') + @name + ' - ' where SysID=@SysID
	select @n = @n-1
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:25:23

i think a much better approach for dealing with your case is to create a udf rather than use a loop. see below


CREATE FUNCTION ConcatValues
(@SysID int)
RETURNS varchar(8000)
AS
BEGIN 
DECLARE @NameList varchar(8000)

SELECT @NameList= COALESCE(@NameList,'') + name + ' - '
FROM #tmpHRSecurity 
where SysID=@SysID
RETURN @NameList
END

update tblHRFilePlan set security = dbo.ConcatValues(SysID)

meustace
Starting Member

2 Posts

Posted - 2009-05-21 : 04:58:42

Excellent thank you! I still don't know why the first syntax did not work, but apart from having to change the temporary table to a normal table because you can't use temp tables in a udf, you were spot on with the use of the function being better.

In this case I tried with your first suggestion which was succesfull and it returned in 20 seconds. Using a UDF returned in 11 seconds, which is a considerable improvement. :-)

Thanks again.
Matt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources