Building a series of strings while in a cursor or while loop
-
Well, I've hit my head on the wall enough times today so I think I'll present my issue to my friends out in SQL ServerCentral land for help.
What I'm trying to accomplish is a way to build a string from a select statement while in a cursor or while loop. To begin, what I do is take in an asterick delimited string and parse it into a temp table. I then determine based on what I was passed what types of predefined searches I can perform, where the search having the highest priority runs first. If there are no hits on the first it trys the second search and so on until there is a hit or we reached the end of the possible searches. There are approximately 5 searches that can be performed based on what was passed in. Unfortunaltely this is not a web based app so we don't have the luxury of having the front-end determine what types of searches to run. So far I have the parsing and being able to determine what available searches I have based on what was passed in. What I don't have is the concatenation of the parsed data based on the given search. Here is a quick view of the types of search and what I'm after:
search_id search_desc
1 memid/Lname/Fname/DOB
2 Fname/Lname/DOB
3 SSN/Lname/fname/DOB
4 MemID/DOB
To meet a given search all elements of the given search need to be supplied or it does not qualify. For example, based on the above table if I am passed all elements that meet search id 1 as in,'3364a*doe*jane*19231103'. I can then determine what other searches are available based on what I have, in this case I qualify for search 1, search 2 and search 4. Search 3 does not apply because I do not have an SSN in the string I was passed. So based on this I should be able to construct three new tab delimited strings to pass to my search sproc. If the first string fails it calls the second, until a hit is made or we reached the end of the possible list of searches. This is what I'm looking for:
Exec sp_name @search_id, '3364a doe jane 19231103'
Exec sp_name @search_id, 'doe jane 19231103'
Exec sp_name @search_id, '3364a 19231103'
Unfortunatley what I end up with, when I'm build the string is the following:
3364a doe jane 19231103
3364a doe jane 19231103 doe jane 19231103
3364a doe jane 19231103 doe jane 19231103 3364a 19231103
So as you can see I am just concatenating each string based on the search_id. I know I'm close but I'm at a loss so If there is someone out there that can help I'd be a very happy man.:-)
Here is the while loop that "should" build the string:
DECLARE @counter INT
DECLARE @int_sche_id INT
DECLARE @Count_Total INT
DECLARE @int_srch_id INT
Declare @baby_srch_string varchar(1000)
Select @counter = 0
SELECT @int_srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
Set @baby_srch_string = ''
WHILE @int_srch_id IS NOT NULL
BEGIN
SELECT @baby_srch_string = @baby_srch_string+srch_val +Char(9)
from ##srch_val
JOIN srch_elmt_assoc on srch_val_id = srea_sche_id
Where srch_val_id in (Select srch_val_id from ##Srch_val where srch_val IS NOT Null)
AND srea_srch_id = @int_srch_id
Select @baby_srch_string
SET @counter = @counter + 1
SELECT @int_srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
and sa.srea_srch_id > @int_srch_id
END
-
Set @baby_srch_string = ''
WHILE @int_srch_id IS NOT NULL
BEGIN
SELECT @baby_srch_string = @baby_srch_string+srch_val +Char(9)
from ##srch_val
JOIN srch_elmt_assoc on srch_val_id = srea_sche_id
Where srch_val_id in (Select srch_val_id from ##Srch_val where srch_val IS NOT Null)
AND srea_srch_id = @int_srch_id
Select @baby_srch_string
SET @counter = @counter + 1
SELECT @int_srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
and sa.srea_srch_id > @int_srch_id
Set @baby_srch_string = ''
END
-
Sorry for the "War and Peace" and not sending the create table statements with inserts to make it easier to reproduce. I'll make sure to do this the next time I post. At anyrate your suggestion solved my issue.
Thank you for responding
Demicoq
-
demicoq,
I noticed that you were using the name sp_name for your stored procedure. I would highly suggest you don't use sp_ as a prefix to your stored procedures!
FROM SQL Books Online:
System Stored Procedures
Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
- The stored procedure in the master database.
- The stored procedure based on any qualifiers provided (database name or owner).
- The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply