Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

Looping in stored procedures

Hi

On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.

All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.

What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.

What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.

I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.

Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!

Thanking you in advance.

SteveSteve:

You have to use a FETCH statement to loop through a result set in SQL Server. However this is VERY inefficient. It is VERY slow. I would not reccomend it to anyone unless it is absolutly necessary.

On the other hand, you should be able to accomplish the search using a query. If you can post some data from your tables and the struc of the tables then I would be glad to help you write a query to get what you need.

HTH|||Hi Wes

Thanks for the reply and offer to help.

I have got two tables:
tblSTATISTCS and tblFACTFILES

tblSTATISTICS is made up of four columns which are ID(Primary Key), Type (different methods of finding a course i.e. search, courselist ect..), Name (what user typed in or selected) and Date

I have now run a view called queSEARCH which picks out all the distinct names which have 'search' as its type. So in effect tblSTATISTIC ahs gone from

ID Type Name Date
1 Search it 11/11/03
2 Courselist Business 11/11/03
3 Search Art 11/11/03
4 Search Web Design 11/11/03

to... queSEARCH

 Name
 it
 Art
Web Design

So I guess what I need to do now is take each row in queSEARCH and use a CONTAINSTABLE statement for tblFACTFILE in a loop?

tblFACTFILE contains quite a few rows so I have shorted this down to the important ones

Course Code CourseTitle Keywords
10001 Art for beginners Artist, painting, drawing
10002 Computing for beginners Web Design, IT, PC
10003 HND in Computing IT, PC, ASP

I hope this makes sence and thanks again for you offer of help.

Steve|||Steve:

I was following you up until the end. What is the end result that you are looking for? I am sure we can do this in one query rather than a loop.|||Wes

What I want is to find all of the word in queSEARCH which do not match anything in either coursetitle or Keywords. So if the word potatoes was added to the list of words people searched with and it was run against the courses I listed earlier, potatoes would remain in the list. Where as everything else (art, it, web design) wouldn't because it matches at least one of the factfiles.

In effect I would like to produce a page of results to show potential keywords that we are not using already within our factfiles.

I hope this is a little clearer.

Cheers
Steve|||You might be better off constructing a SQL string and sp_executing it.|||I think I am now at the point you were at when you started this thread. Due to the result set that you want it can't be done using a sub query (which I am sure you already knew). I now understand you question perfect, in that you must use a Fetch loop to go through the queSearch table one row at a time and then query to tblFactFile to see if there are any matches. You are going to have to use the CONTAINS clause rather than the CONTAINSTABLE because you can use a variable with the CONTAINS where you can't with the CONTAINSTABLE. Here is the code that you were originally looking for:

DECLARE @.currentName as varchar(50)
DECLARE myCursor CURSOR FOR
SELECT [name] FROM queSearch
--your cursor is now filled with the queSearch results
OPEN myCursor
-- Perform the first fetch.
FETCH NEXT FROM myCursor INTO @.currentName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
 Select CourseCode FROM tblFactFile WHERE CONTAINS(Keywords,@.currentName) OR CONTAINS(CourseTitle,@.currentName)
 IF @.@.rowcount = 0
 BEGIN
-- IT did not find any matches, therefore do what you need to do
 END
 -- This is executed as long as the previous fetch succeeds.
 FETCH NEXT FROM myCursor INTO @.currentName
END
CLOSE myCursor
DEALLOCATE myCursor
GO

|||Thanks Wes.

I will give this a try tomorrow and let you know how I get on.|||Wes

That peice of code work great. Thanks very much for taking the time to help.|||I am glad to help.

LookupCube: Unexpected Results with StrToSet

Hi.
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Member
s})")))} ON
ROWS
FROM [Sales]
Does anyone have any idea why the above query fails with an "Unknown
internal error"? When I replace the "[Product].Members" string with
"[Time].Members", it works fine.
The following query, which I think is quite similar, works fine:
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet("[Product].Members")} ON ROWS
FROM [Sales]
Thanks.I believe this happens because the list of product members exceeds the
maximum string length that can be handled by "SetToStr", "LookupCube", or
both.
This is supported by testing your query replacing [Product].Members by
Head([Product].Members, n).
Up to n = 344 this works, then it breaks.
At this point , the string length should be over 32K, which is a likely limi
t.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html
"John" wrote:

> Hi.
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Memb
ers})")))} ON
> ROWS
> FROM [Sales]
> Does anyone have any idea why the above query fails with an "Unknown
> internal error"? When I replace the "[Product].Members" string with
> "[Time].Members", it works fine.
> The following query, which I think is quite similar, works fine:
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet("[Product].Members")} ON ROWS
> FROM [Sales]
> Thanks.
>
>

Looking for opinions.....want to use SQL Server to store images

I have a client who wants to be able to upload images to his website for his customers to access. It will probably max out at 100 images a month...so not a huge amount of data. I am using asp.net 2.0 and SQL Server 2005.

Does anyone have thoughts or opinions on why I should or should not take this approach?

You could store images on disk or you could store images in SQL server.
It would seem that SQL would require more overhead than Disk so...

Does SQL offer you something that you require that disk storage does not?

If it does, then use it. Pulling images from a db is not an uncommon practice. The small performance drop could be mitigated by caching the images (in-memory for small images or disk for large images).

If nothing you are doing actually requires SQL server though, i would stick to disk.

|||I would use SQL Server. It simplifies quite a bit, and you don't have to give users write permissions any where on the web server making it more secure.|||

I am already using sql server for other reasons so I wouldn't be setting it up specifically for this. Also I would think that sql server would organize the files for you, where they live and whatnot, thus less that I would have to deal with from a programmatic standpoint, not to mention the directory permissions as mentioned above. The images that will be stored will be a little larger than a business card.

Thanks for your responses..