Convert Code from VarChar to Text

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

SQL Server Development (2000)

Convert Code from VarChar to Text

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-11 : 12:43:41

-- I need to convert the code in this sp from Varchar(8000) to text or ntext
-- as the size of the passed value exceeds 8000 char's.

-- Purpose: parce comma delimited values i.e. 123,546,234,890 ...

-- I have looked at the BOL which is great for examples of storing the data to a
-- table however the passed value does not get stored it is used as a loop to
-- create individual values. I will also be eliminating the cursor with a temp table

--Old Code
--The top 4 variables are passed as parameters to the sp

declare @login_id varchar(10)
declare @application_id varchar(10)
declare @group_id varchar(10)
declare @user_ids VarChar(8000) -- need to pass as ntext or text

declare @user_id int
declare @user_id_tbl table (user_id varchar (10))

declare @t_date datetime

declare @string varchar(8000) -- need to pass as ntext or text
declare @substring varchar (100)
declare @substrlen int
declare @cindx int
declare @flag int

set @flag=1
set @t_date = current_timestamp
set @string=@user_ids

if exists(select * from [group] where group_id=@group_id and status=1)
begin
set @cindx = charindex(',',@string)

while @cindx <> 0
begin
set @substring = substring(@string,1,@cindx)
set @substrlen = len(@substring)
if len(replace(@substring,',','')) > 0
begin
insert @user_id_tbl
values (replace(@substring,',',''))
end
set @string = rtrim(substring(@string,@cindx+1,7000))
set @cindx = charindex(',',@string)
end
--if len(cast(@string AS datetime)) > 0
if len(@string) > 0
begin
insert @user_id_tbl
values (@string)
end
declare user_id_cursor CURSOR for select * from @user_id_tbl
open user_id_cursor
FETCH NEXT FROM user_id_cursor into @user_id
WHILE @@FETCH_STATUS=0
begin
if exists(select * from login where login_id=@user_id)
begin
if @flag=1
begin
delete from Login_group where group_id=@group_id
set @flag=0
end
if not exists(select * from Login_group where login_id=@user_id and group_id=@group_id)
insert into Login_group(login_id, group_id, status, updated)
values( @user_id, @group_id, 1, @t_date)
end
else
begin
select 0 as result
end
FETCH NEXT FROM user_id_cursor into @user_id
end
CLOSE user_id_cursor
DEALLOCATE user_id_cursor
select 1 as result
end

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 14:26:38

See if this helps you:

http://sqljunkies.com/WebLog/amachanic/articles/SplitString.aspx

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-11 : 15:37:37

I will test and reply... The link looks valuable Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources