November 15, 2007 at 2:33 pm
I'm developing reports and custom utilities on a third party application database. The application manages different cases (bunch o' info). For each case, it creates about 24+ tables with unique names based on the case number. (Editorial comment: The creators of this application have never heard of the word normalization, but I digress...)
So, for my utilities I want to create generic stored procedures that accepts the case number as a parameter. From that, I dynamically create T-SQL commands in a variable (eg, @query) and execute them via 'exec sp_executesql @query [optional params]'. So far, I've been successful in making this happened.
However, for my latest utility, I need to dynamically create a cursor. For example...
set @myCursor = cursor for
select Col3 from Case4_TableA
where Col2 is not null;
Clearly that is hard coded. What I want is something like this...
set @query = 'cursor for
select Col3 from Case + ' @caseID + '_TableA
where Col2 is not null';
exec @myCursor = @query
When I try that the error is (something like) "A cursor variable is not allowed in this context."
Any help would be appreciated! Thank you!
November 15, 2007 at 2:54 pm
Not sure, but I think dynamically creating a cursor is like dynamically creating a temp table... you may get it to work, but it'll likely be out of scope just as soon as the dynamic SQL executes.
I realize you've greatly simplified the code example (Thank you!)... but, why do you need a cursor for this? What is it that the cursor will really do that you don't think can be done using some non-cursor code?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2007 at 5:53 am
The only way to do it is to build it out completely, but I have to agree, are you sure you need a cursor?
Here's an example, ugly though it is:
DECLARE @caseid INT
DECLARE @query NVARCHAR(MAX)
set @query = N'DECLARE CurrencyList CURSOR FOR
SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE ''%Dollar%'';
OPEN CurrencyList;
FETCH NEXT FROM CurrencyList;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Normally there would be operations here using data from cursor
FETCH NEXT FROM CurrencyList;
END
CLOSE CurrencyList;
DEALLOCATE CurrencyList;
'
EXEC sp_executesql @query
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 16, 2007 at 6:21 am
Thanks Grant! I may go that route. But, just to provide a comprehensive problem statement, please see my next post.
November 16, 2007 at 6:25 am
Okay, here goes, I'll give it my best shot to describe this. There are four tables involved. (Reminder: This is a third party application. I can add tables and columns but I cannot restructure what is there.)
Tables
tKeyword: Contains a list of keywords that must be identified in the document text table (tDocText). Has status column (called status) that needs to be updated upon successful processing.
tDocs: A table that holds information (attributes and metadata) about documents. One of its columns is updated. We'll call this column attribute1 and it is an ntext datatype. This column may hold zero or more values with each value delimited by HTML-style tags. For example:
tTextDocs: A table that holds the text (OCR) of the documents listed in tDocs. The text is held in an ntext column (called theText). In this problem, this table is only read from.
tDocs_attribute1: This table holds an entry for every value in the docAttribute1 column of the tDocs table. Its columns are DocID, SequenceNumber, Term. TableFk is the reference to the entry in the tDocs table; SequenceNumber is the zero-based number of the value in the docAttribute1 column; and Term is the value (eg, Val1) in the column. However, in this Term column, the value is not delimited by tags.
Sample Data
tKeyword
id keyword status
1 FooBar {null}
2 George {null}
3 Frank {null}
tDocs
id title attribute1
10 Meeting Minutes
11 Sales call notes {null}
12 Project Proposal {null}
tTextDocs
id docID theText
20 10 '16 November 2007 Project FooBar Meeting In attendance...'
21 11 '16 November 2007 Called Company XYZ to discuss...'
22 12 '16 November 2007 Project Proposal Our company is...'
tDocs_attribute1
docID seqNum Term
10 0 FooBar
10 1 George
10 2 Frank
For document DocID=10, I've shown what the result of this task should be along with its associated entries in tDocs_attribute1.
If you're still reading this, you're a brave soul. So, given the above, here's the task:
Iterate through the entire tKeyword table for each entry in the tDocs table. If the keyword is found in the text of the document as found in the tTextDocs table, then add that keyword to the attribute1 column of the tDocs table and add a new entry in the tDocs_attribute1 table, making sure the seqNum is correct. Once the keyword has been processed, update the tKeyword status column as 'done'.
As you can quickly see, there is the cross product (for lack of a better term) with the tKeyword table and the tDocs table. Hence, currently, I have a nested cursor solution and it works.
What I want to do is to write a generic stored proc to make this happen rather than copy-paste-edit a new one for each case (see my original post) that is created in the database.
Whew! I think that's it. If you can provide a set-based, non-cursor solution, I'm all for it! Please share your insight. Thanks!
November 16, 2007 at 7:57 am
How about something like this? It doesn't provide for a zero based sequence number, but it does put the numbers in the correct order. Indexing could be an issue to. Also, I did it with all inner joins. I suspect one or two of those would work better as an outer join. I don't know. It's a first blush pass at the issue. I'm probably missing a point or two.
INSERT INTO
SELECT tDocs_attribute1
(docID,seqNum,Term)
SELECT d.Id
,ROWNUM() OVER(ORDER BY k.Id)
,k.keyword
FROM tDocs d
JOIN tTextDocs t
ON d.Id = t.DocId
JOIN tKeyword k
ON t.theText LIKE '%' + k.keyword + '%'
Then, after you get the list of stuff into tDocs_attribute1, do a second pass & update the tKeyword as done where there's a batch.
Oh, and before we miss this VERY important point... EWWW, you've got Hungarian notation to designate tables... Yuck!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply