I have an application that needs to create invoices on a daily basis to multiple clients based on orders shipped that day. This is easy to do on the front-end. But how can I do this on the back end in SQL Server.
I want to sort orders by Client ID and put all orders belonging to one customer on one invoice. When customer id changes, I change the Invoice ID. Is this possible in SQL?
Xcog
Hi there,
I need to create a stored procedure that I can pass in an arbitrarily long list of record IDs and have an SQL statement executed for each one.
For instance, I want to be able to pass in 1, 2, 5, 78, 100, 216 and have it automatically execute:
UPDATE table SET value='blah' WHERE id=1; UPDATE table SET value='blah' WHERE id=2; UPDATE table SET value='blah' WHERE id=5; UPDATE table SET value='blah' WHERE id=78; UPDATE table SET value='blah' WHERE id=100; UPDATE table SET value='blah' WHERE id=216
Can anyone please show me how I would structure a stored procedure like this? Specifically, how would I pass the list of IDs in as a parameter, and how would I iterate through them.
Much appreciated!!
Is there a key word one can use to immediately jump out of an iteration when using a cursor, and move to the next record using 'fetch next' cursor?
View 1 Replies View RelatedA Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.
So, two questions
1. What is the form of an iterative loop in a Stored Procedure?
2. How do you call a Stored Procedure from inside another?
Thanks, td
Okay I'm going nuts.
I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?
::grins::
Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.
Arrrg!
~EHunter
I am having a mental fart...
I have two tables:
DECLARE @store_options TABLE(store_option_id INT IDENTITY(1,1), store_id INT)
DECLARE @vendor_options TABLE(vendor_option_id INT IDENTITY(1,1), store_option_id INT, item_id INT, vendor_id INT, price NUMERIC(18,4))
I populate the first table with a litst of stores that offer all desired items.
I populate the second table with a list of vendors, the item is, and cost avaiable at each of the stores in the first table.
What I would like is to output all possible the store and vendor combos ordered by combined price.
So, for instance, I have 3 products, A B and C. Store X has A and B by vendor G, and A B and C by vendor H. I want the output to have all iterations of (Store, Product, Vendor, Price) grouped in order of total price. So...
X A G
X B G
X C H
X A G
X B H
X C H
X A H
X B G
X C H
X A H
X B H
X C H
ordered by each group's combined price.
For some reason, I can't get this straight in my head. Must need more coffee.
How can I force a Next Iteration in a ForEach Loop container?
I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).
If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.
Is it possible to force "Next Interation' on the parent container?
Thanks - Covi
I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?
In sudo language, I'd like to do:
Code Snippet
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub
Somehow, I'd like to be able to do that without specifying each field individually:
row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...
How can I force a Next Iteration in a ForEach Loop container?
I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).
If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.
Is it possible to force "Next Interation' on the parent container?
Thanks - Covi
Hey guys, wonder if you could help.
I have a flow which is within a foreach loop. The first box in the flow is a scrip component which makes some check in a database.
Is there a way to say, inside that script component: 'stop the flow here, don't bother going onto the next box, and go on to the next iteration'?
thanks!
andy
I 've have a stored procedure that compares fields across databases.In order to do so it requires 2 values it acquires from 2 tables. Thesearch is based on the ID of the data owner and a subject:proc_evaluate_results @StudentId = '222222', Course = 'PSY101'In order to obtain those values I run a cursor accross my records andSELECT THEM INTO 2 @variables, which then replace 222222 and PSY101with dynamic values eg.--define a cursor etc.etc.WHILE @@cursor_fetch = 0BEGIN--do the cursor call INTO @varsEXEC proc_evaluate_results @StudentId = @studentID, @Course =@CourseCodeENDNow,the vars are being passed to the stored procedure and executed OK,but the cursor gets stuck on the last record and continues to evaluateit until stopped manually.If I comment out the EXEC and replace it with eg. PRINT @Course + ' |' + @CourseCode it runs fine, exiting after the last record.ThanksR>
View 6 Replies View RelatedHow do I change the color of the task icon back to green? I have and FEL with tasks in it that occessionally fail. The error is trapped to allow the container to continue processing. I would like to change the color of the icon back to green on the next successful iteration of a task but I haven't found a way to do it.
View 7 Replies View RelatedHi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.