need advice with logic - SQL Server Forums

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)

need advice with logic

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-01-20 : 18:37:51

ASP: I have one long string (123abcdef456) I will be parsing based on the lengths and starting positions returned by a select stored proc. Recordset returned could be like this:
starting pos. length
1 3
4 3
7 3
10 3

I will pass the parsed result in a CSV to a second SP, and it would look like this: 123,abc,def,456

SP:
in a loop,
parse this CSV
do a select on a description table1 where txtDesc = 123
(on second loop, table2 where txtDesc=abc; on third, table3 where txtDesc=def ...)
store the value in a temp table (index, desc)
end loop
select all desc from temp and return recordset to ASP page

My question is: Is this the best way to go about this? I wonder if I could combine the stored procs into one without using cursor.

BSmith
Starting Member

6 Posts

Posted - 2003-01-20 : 19:12:41

ok so you've got 2 inputs - the string and a set of delimiter params, and you want a result set returned that breaks the string into rows for each substring?
If you can put the delimit params in a table you can just select the delimited string portion:

create table Delims(Start int,Length int)
insert into Delims values(1,3)
insert into Delims values (4,3)
insert into Delims values(7,3)
insert into Delims values(10,3)

Declare @String as varchar(8000)
set @string = '123abcdef456' --this would be passed into SP

select substring(@string,start,length)
from delims

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-01-21 : 00:58:24

quote:

create table Delims(Start int,Length int)
insert into Delims values(1,3)
insert into Delims values (4,3)
insert into Delims values(7,3)
insert into Delims values(10,3)

Declare @String as varchar(8000)
set @string = '123abcdef456' --this would be passed into SP

select substring(@string,start,length)
from delims

Very nifty!

Hey Peter, search this site for CSV, you will enough material to read through the night!

OS

BSmith
Starting Member

6 Posts

Posted - 2003-01-21 : 22:58:33

quote:

Very nifty!

Hey Peter, search this site for CSV, you will enough material to read through the night!

Thanks, its amazing what you can do with the select statement if your not obsessed with cursors or even pivoting CSVs for that matter...?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources