updating string value to database

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)

updating string value to database

Author

Topic

sundaram123
Starting Member

15 Posts

Posted - 2004-11-10 : 22:59:00

Hi all,
I have following procedure.


CREATE PROCEDURE createWBS
AS BEGIN
	
	DECLARE staffCrsr CURSOR FOR select [id] from staff where ( manager is null or manager = 0) and wbsflag = 0;
	
	declare @mid int;
	declare @seq int;
	declare @prefix char;
	
	open staffcrsr;
	fetch next from staffCrsr into @mid;

	set @seq = 1;	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		If @seq <= 9 
             	 	set @prefix = '000' & @seq;
	        else 
		    	if @seq <= 99 
            			set @prefix = '0' & @seq;
		        Else
		             	set @prefix = @seq;        
			
		update staff set wbs=@prefix where id=@mid;
		set @seq = @seq + 1;
	   	-- This is executed as long as the previous fetch succeeds.
		fetch next from staffCrsr into @mid;
	END

	CLOSE staffcrsr;
	DEALLOCATE staffcrsr;

END 
go

if seq varibale value is less then 9, I am adding two 00, its less then 99 adding one 0.

after executing this procedure, i would except wbs column vlaue like this 001, 002, 003 etc.

But I am seeing all 0 values.

Any idea, what I am doing wrong on this code?

Thanks

sundaram123
Starting Member

15 Posts

Posted - 2004-11-10 : 23:21:07

hi,

Now I changed my procedure like this. now its updating wbs filed value to 6. I want to padd two zeors infront of 6 (006).


declare @seq int
set @seq=6
update staff set wbs = case when @seq < 9 then '00'+ '' + @seq when @seq < 99 then '0' + @seq else @seq end where id=390
go
select wbs from staff where id=390

output:
6

sundaram123
Starting Member

15 Posts

Posted - 2004-11-10 : 23:30:42

Hi,
I finally got the solution, by reading some man pages:

declare @seq int
set @seq=6
update staff set wbs = case when @seq < 9 then  
				cast('00' as  varchar(2)) + cast( @seq as varchar(1)) 
			     when @seq < 99 then  cast('0' as varchar(1)) + cast(@seq as varchar(2))
				 	else 
				  cast(@seq as varchar(3)) 
			      end 
			where id=390
go
select wbs from staff where id=390

output:
006

Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 02:55:34

If @seq will never be greater than 999

quote:
cast(@seq as varchar(3))

Why dont you just do
update staff set wbs = CONVERT(varchar(3),RIGHT(1000 + @seq,3))

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources