February 23, 2014 at 9:50 pm
Ok all,
So now I have a cursor of data that I want to write out using a stored procedure. How can I do that without the RBAR process of a do while loop?
Here is how I would do it...:
declare @parm1 int, @parm2 int, @parm3 char(30), @parm4 int
- Get cursor
Do while fetch > 0
call usp_make_record parm1 parm2 parm3 parm4 parm5 parm6
end
Can this be done via a set? Note.. the stored proc will have the fields as parameters so that multiple functions can call this routine. I can't pass in a table variable from a ODBC input (since this is one of the ways I want to call the function).
Thanks
Mike
February 24, 2014 at 2:03 am
mike 57299 (2/23/2014)
Ok all,...a cursor of data that I want to write out...
Could you be a little more specific e.g. how many rows? What's the client?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 24, 2014 at 7:45 am
Row count could be from a few dozen to 1000s. The process will be called by SQL internally and by Visual Foxpro externally via the ODBC driver.
February 24, 2014 at 8:53 am
Have you tried working with the SqlBulkCopy objects in .Net? I use them with OLEDB connections and they are very for large data sets.
A second approach is to use a processing table(s) to hold the data you want the stored procedure to process. I use the approach as little as possible, but it works when I need it. You need a sequence of some type, normally I use another table with an identity column, to get a unique value that all records for the batch will be assigned. Next, load the data. Last call the proc with the ID you created in step one. The proc will pull data from the table for the ID, do the process, then delete from the queue to clean up. If you use a table in step one, you can log status, etc, as you process, which is very handy to debug issues later on.
February 24, 2014 at 8:55 am
You can't chage that code to make it set based. You need to change the code from usp_make_record to accept several rows at a time.
February 24, 2014 at 9:25 am
Luis Cazares (2/24/2014)
You can't chage that code to make it set based. You need to change the code from usp_make_record to accept several rows at a time.
Other than passing in a table variable - how can I make the code accept several rows?
February 24, 2014 at 9:35 am
mike 57299 (2/24/2014)
Luis Cazares (2/24/2014)
You can't chage that code to make it set based. You need to change the code from usp_make_record to accept several rows at a time.Other than passing in a table variable - how can I make the code accept several rows?
Not so long ago I worked on a project where there was a similar requirement. The fastest way we found to make it work was
Create a global ##temp table with a uniquified name
Use sql pass-thru to shove VFP data into it
Call a stored procedure to process the data and cleanup
Given time I'll remember a few more details.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 24, 2014 at 9:49 am
I will post this to my blog with more descriptions, but here is some test code. We use this style when users have the ability to upload, make minor revisions, then deploy the update.
create table Users (
UserID int,
UserName varchar(32),
IsValuedMember bit
)
go
insert into Users values
(1,'Eric',0),
(2,'Bob',0),
(3,'Fred',0)
go
create Table UpdateQueue (
UpdateID int identity(1,1) not null,
UpdateStartTime datetime,
UpdateEndTime datetime,
constraint PK_UpdateQueue primary key clustered (UpdateID)
)
go
create table MultiUserUpdate (
UpdateID int,
UserID int,
IsValuedMember bit
)
go
create proc MultiUserUpdate_Begin (
@UpdateID int OUTPUT
)
as
insert into UpdateQueue (UpdateStartTime) values (getdate())
select @UpdateID = @@identity
go
create proc MultiUserUpdate_Rows (
@UpdateID int,
@user-id int,
@IsValuedMember bit
)
as
insert into MultiUserUpdate values (@UpdateID, @user-id, @IsValuedMember)
go
create proc MultiUserUpdate_Process (
@UpdateID int
)
as
update Users
set IsValuedMember = MU.IsValuedMember
from Users U
join MultiUserUpdate MU
on U.UserID = MU.UserID
where MU.UpdateID = @UpdateID
Update UpdateQueue
set UpdateEndTime = getdate()
where UpdateID = @UpdateID
go
--- How to avoid passing tables in a PROC
create proc UpdateAllMembersToValuedCusomter
as
DECLARE @UpdateID int
--Get the update ID
exec MultiUserUpdate_Begin @UpdateID OUTPUT
--populate the queue
insert into MultiUserUpdate
select @UpdateID, UserID, 1
from Users U
where U.IsValuedMember = 0
--Process the queue
exec MultiUserUpdate_Process
---FROM Front End Code ---
DECLARE @UpdateID int
exec MultiUserUpdate_Begin @UpdateID OUTPUT
EXEC MultiUserUpdate_Rows @UpdateID, 1,1
EXEC MultiUserUpdate_Rows @UpdateID, 2,0
EXEC MultiUserUpdate_Rows @UpdateID, 3,0
exec MultiUserUpdate_Process @UpdateID
February 24, 2014 at 10:00 am
I can't see the code for that SP, I have no idea on what you could improve.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply