Can anybody see anything wrong with this SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can anybody see anything wrong with this SP?

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-03-15 : 09:13:52

I have the following stored procedure:-

CREATE     PROC usp_UpdateStaffSIDs
AS

-- empty the temp holding table
TRUNCATE TABLE dbo.MigratingUsers

-- copy the entries in the CSV file to the temp holding table
BULK INSERT dbo.MigratingUsers FROM 'c:\sids.csv'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

-- iterate through the MigratingUsers table
DECLARE @SID nvarchar(100), @Username nvarchar(50)

DECLARE curUsers CURSOR FOR
SELECT UserID, NetworkID FROM dbo.MigratingUsers

OPEN curUsers

-- Fetch the first row
FETCH NEXT FROM curUsers
INTO @SID, @Username

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
	-- update the SIDs for matching users
	UPDATE dbo.lspUsers
	SET dbo.lspUsers.UserID = @SID, dbo.lspUsers.Migrated = 1
	WHERE dbo.lspUsers.Migrated = 0 AND dbo.lspUsers.NetworkID = @Username
	
	--PRINT @SID
	--PRINT @Username
	-- This is executed as long as the previous fetch succeeds.
	FETCH NEXT FROM curUsers
	INTO @SID, @Username
END

CLOSE curUsers
DEALLOCATE curUsers

What I want to be able to do is load data in from a CSV into a temp table called MigratingUsers. I then iterate through each row in this table and update the UserID column for each matching NetworkID. It performs every task OK, without error with the exception of the UPDATE part. All I get back is, (0 rows affected) :(

I probably can't see it for looking at it!

Kristen
Test

22859 Posts

Posted - 2005-03-15 : 10:37:04

The users aren't already set to Migrated?

I would do this without a cursor - it will be much faster


UPDATE U
SET   UserID = MigratingUsers.UserID, 
      Migrated = 1
FROM  dbo.lspUsers U
      JOIN dbo.MigratingUsers
           ON MigratingUsers.NetworkID = lspUsers.NetworkID
WHERE dbo.lspUsers.Migrated = 0

Kristen

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-03-15 : 10:55:06

If I try and run that, I get:-

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.lspUsers' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'lspUsers' does not match with a table name or alias name used in the query.

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-03-15 : 11:49:21

Figured out what was wrong, d'oh!!

The columns in the MigratingUsers were around the opposite way from the relevant ones in the lspUsers table

Thanks.

Kristen
Test

22859 Posts

Posted - 2005-03-15 : 13:30:12

I did wonder about using @SID and @Username, but I assumed you had a reason for that!

I try to call my @Variables the same as the column they represent, and that kinda brings those sorts of errors to light.

But I still think you should use an UPDATE rather than a CURSOR ... You'll just need to fix my flawed logic ...

Kristen

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-03-15 : 14:17:41

Kristen - If this routine was going to be a day-to-day business requirement, then I would optimize it as much as possible but it's only really a procedure to update approx 25 rows at a time due to a domain change.

Thanks for your help :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-15 : 17:23:20

Umhhh,
I think Kristen's non corsor approach is faster, cleaner, more readable, and easier to maintain than the cursor one.
And the reason to NOT make it setbased is that it only runs once in a while with a small amount of rows, so we won't bother.

Ok, move on.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 01:36:41

OK, I'll take off my Nice Friendly Smile !

And the Cursor approach introduces code complexity, and the liklihood of bugs etc.

For me its more a case of adopting a programming style which is "defensive"

Oh, "move on" you said? Right Ho!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-16 : 02:06:54

>> Oh, "move on" you said? Right Ho!
you are talking to me?

What I meant was that it's not justifiable to stick with the cursor.
But when one faces insurmountable thickheadedness, one might just as well "move on".

Sorry mparter, but drop the cursor.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 03:23:48

"you are talking to me?"

Nah, not specifically Rocky. But I was on the same page in the hymn book as you! and I took your advice too ...

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-16 : 03:34:47

Ah ok...
i am lening english from e bok

(PS.You did watch Faulty Towers, didn't you?)

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:08:51

"e bok" - you mean "e-book"? Will that be BoL by any chance?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-16 : 09:17:10

touché

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources