declare cursor with dynamic Select
-
Hi Guys,
I have to build a select statement as a varchar (@SQL) then iterate through it one line at a time
I cant DECLARE DBCursor AS CURSOR FOR @sql
Or any other variation I've tried.
If anyone could point me the right way I'd be grateful
Cheers
Robert
-
Are you absolutely sure that you have to use all those "bad" things?
Cursor and dynamic SQL?
Can you expand a bit on what you are trying to do?
/Kenneth
-
Hi Kenneth,
The dynamic Select is a collection of IF statments that make up the where clause. I want to assign each project with an invoice number so intend to order by project number then loop through each line and change the invoice number when the project does. The resultant cursor will then be used to provide report output and INSERT to another table.
Do you need any more info?
Many thanks
Robert
-
If you want to use cursor with dynamic sql then try this
DECLARE @sql nvarchar(4000)
SET @sql = 'DECLARE mycurs CURSOR FOR SELECT col,col,col FROM
'
EXECUTE sp_executesql @sql
OPEN mycurs
FETCH NEXT FROM mycurs INTO ...
CLOSE mycurs
DEALLOCATE mycurs
Far away is close at hand in the images of elsewhere.
Anon. -
Read http://www.sommarskog.se/dynamic_sql.html and decide if that's the way for you to go. Erland has there a chapter on "dynamic cursors".
But I'm with Kenneth, each of both things isn't optimal, but combined
You might also want to read Erland's article on dynamic search conditions. Maybe you get someideas from it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks to all.
I'll read the doc and see if there are better ways to do this. I tried to search for such a document, but could only get the first screen of the search results, blank after that.
Thanks again
Robert
-
Please give sample data and what you want to do.
I guarantee you need neither dynamic SQL, nor a cursor.
Are you familiar with the UPDATE statement in T-SQL? It allows you to instantly update entire sets of rows at once, instead of using a cursor to process one row a time. Much, much, faster and more elegant.
A tool of the jedi!
-
Hi Yoda,
I'd love not to use a cursor. Yes I am aware of UPDATE.
I just want to assign a consequetive number to each row of a temporary table. The number has to increment at each change of a field (Project Number)
I do need dynamic SQL as this is all part of a report engine.
Cheers
Robert
-
Yoda is confused -- are you updating/changing data in your database, or just producing a report? (don't say both -- these are two completely separate concepts!)
If it is just a report, how are you outputting this report? by what method (i.e., resporting services, HTML, VB, crystal, access, Excel)
much, much easier to increment numbers and display them at the presentation layer. this is trivial with all of the products mentioned above; much more complicated to do in T-SQL.
-
Yoda - Oh how right you are!
Unfortunatly it is both. These are invoices that are being produced with Crystal and CSV output and inserting into a transaction table. I dont trust my crystal to ensure the numbers are the same as the CSV and transaction update therefore I want to generate the number first.
Cheers
Robert
-
I'd like to help if you can give more details of this process. tell us step by step what you are tyring to do.
i.e.,
step 1 -- import CSV file
step 2 -- store in a table
step 3 -- insert into "Invoices" table, calc Invoice ID
step 4 -- produce crystal report
something like that, be brief but detailed, with examples as necessary. sample data helps also.
-
Step 1 -- Receive selection criteria from Report Engine and parse as parameters into procedure
Step 2 -- Create Select Statement utilitising parameters
Step 3 -- Assign invoice number to each line of result set (multiple lines per invoice)
Step 4 -- Export CSV File for Accounts package (subset of select query)
Step 5 -- Make Select query available to Crystal to produce invoices (subset of select query)
Step 6 -- Post invoices to internal transaction table (subset of select query)
To do step 3 I create a dynamic cursor based on step 2, fetch each item and assign the invoice number then insert into a #Results table
#Results is then used for steps 4-6
I hope I've made clear enough.
SET @sql='DECLARE DBCursor CURSOR FOR ' + @sql
EXECUTE sp_executesql @sql
Does the trick, but as I agreee, not pretty!
-
Still confused on terminology:
Are you assigning Invoice Numbers or Line/Item Numbers to each line in the resultset for that 1 invoice?
i.e., this process is always generating 1 invoice or multiple invoices? my assumption on terminology is 1 invoice = 1 invoice number, but that doesn't hold consistent with what you are stating. please let me know if this is incorrect.
-
many invoices depending on selection criteria. Result set contains line items for many invoices. Each line items needs to be assigned with an invoice number. Selected orders by project therefore all line items are together to enable an invoice number to be assigned.
-
What is wrong with
ALTER TABLE #Result Add InvoiceNo int Null -- If Necessary only
Update R Set InvoceNo = Next.No
From
#Result R
Join
(Select R1.[Project Number] PN ,( Select Count(Distinct R2.[Project Number])
from #Result R2
where R1.[Project Number] <= R2.[Project Number]) No
From #Result R1
group by R1.[Project Number]) Next
on R.[Project Number] = Next.PN
I believe no cursor is necesary. You Can always amend above to start at last Invoce Number Submitted + 1 if you like
HTH
* Noel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply