Splitting name field into 2 name fields not working 3

[Wyldcard9]

DECLARE @v_pos_FirstNameStart INT
DECLARE @v_pos_LastNameStart INT

UPDATE [Pension Forms].[dbo].[XFRFUND10]
SET
@v_pos_LastNameStart= charindex(' ', MEMBERFULLNAME,1 ),
LASTNAME=left(MEMBERFULLNAME, @v_pos_LastNameStart),
@v_pos_FirstNameStart= charindex(' ', MEMBERFULLNAME,@v_pos_LastNameStart + 2 ),
FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - @v_pos_LastNameStart))


I get:
Server: Msg 536, Level 16, State 1, Line 4
Invalid length parameter passed to the substring function.
The statement has been terminated.

What am I doing wrong?

 

[ESquared]

Karl, just in case you missed it:

The only reason you think the 1st assignment is made last is because your first assignment happens to be a column assignment. The column assignment is *always* made last.

 

[donutman]

create table #Temp (T int)
insert into #Temp select 0
insert into #Temp select 0
insert into #Temp select 0
Declare @i int
set @i=1
Update #Temp
   Set @i=@i+1, @i=T=@i+1
   --Set @i=@i+1, @i=@i+1, T=@i
Select * from #Temp
drop table #Temp

Try that and then switch to the commented set and try again. I think you're right and I think there's a bug. Take out the leading @i=@i+1 from both and it works as you say.
It's as though the 2nd @i+1 is discarded after it is used on the 1st row, which would, of course, make even less sense then what you have uncovered. I don't think it's the 1st @i+1 that gets discarded because I tried other sets.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

This reminds me of a card game I used to enjoy as a kid. I've forgotten the details, but I think the name of it was God. It went something like this:
The dealer (god) would make-up some crazy sequential pattern to follow with the cards (there's plenty of subtle patterns you can create). The objective was to stump the other players as long as possible. God would turn cards up randomly and place them to the right of the previous "turned up" card. The question asked by god was does this card follow the sequence or not. When a player got it right and felt he knew THE rule he could take possession of the deck and sub for god. A clever god could create a pattern within a pattern to fool lesser gods.
I think the game came from Scientific American, because the concept was that it mimicked the search for the "truth" about nature.
BTW, what's was wrong with WyldCard9's original post? With everything we've learned so far, I can't see anything wrong with it. Don't understand the substring reference either.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

WHOA! This is strange.

I agree with you 100%. It appears to use the second @i assignment only on the first row.

What this means to me is just to avoid the @var = Col = Exp syntax. Better to split them yourself and be sure you know what you're getting. See my new thread about an improvement to the FAQ faq183-5021.

 

[donutman]

What about his original post?

 

[ESquared]

Doesn't this work?

UPDATE [Pension Forms].[dbo].[XFRFUND10] 
SET
    LASTNAME=Left(MEMBERFULLNAME, charindex(' ', MEMBERFULLNAME)-1),    
    FIRSTNAME = ltrim(right(MEMBERFULLNAME, LEN(MEMBERFULLNAME) - charindex(' ', MEMBERFULLNAME)))

 

[donutman]

You missed my previous post...what was wrong with his original post?

 

[ESquared]

I forgot you can use the SUBSTRING function to get the remainder when you only know the position from the left (just leave the third parameter empty, I believe).

I can't for the life of me see what's wrong with the first query. Aside from the fact that the firstname variable is never used, it looks okay to me.

I'll have to try it at work when I am back there on Monday.

 

[donutman]

Ok it gets deeper than we thought!

CREATE TABLE #Temp (S int, T int)
INSERT INTO #Temp Select 0, 0
INSERT INTO #Temp Select 0, 0 
INSERT INTO #Temp Select 0 ,0
DECLARE @i as int
SET @i=1
UPDATE #Temp
   SET  @i=@i+1,  @i=S=@i+1, T=@i, @i=@i+5
SELECT * from #Temp
DROP TABLE #Temp

Check the results, you will see that it adds a new wrinkle. But the more I look into it, the more you should avoid it! It gets real funky when you do two double-sets AND you move them around.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]