October 31, 2008 at 1:55 pm
Any ideas how to quickly call a stored procedure to insert a record into another table for each record in one table that has a NULL value in a particular column?
October 31, 2008 at 1:58 pm
Can you explain what you mean by 'quickly call' the SP? The code for inserting from one table to another is simple so I'm a bit stuck here as to what your question entails?
What have you tried already?
October 31, 2008 at 2:12 pm
Well one not so quick way would be to use a cursor on the table with the null values.
October 31, 2008 at 2:17 pm
So you are looking for a stored procedure that can do a set based insert into one table from another. Quickly to you means good performance?
Here's an example of the statement, you can put it into a SP.
INSERT INTO YourTargetTable (Columns......)
SELECT Columns.....
FROM YourSourceTable
WHERE CheckNullColumn IS NULL
Again, the statement itself is so simple, I feel like I'm missing something here. Is this what you are after?
October 31, 2008 at 2:17 pm
Let's break the problem down a little. (let's call this a "hint")
How would you identify the rows in TableA that need the rows? What would the SQL look like (no cursor, just a SELECT)?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 31, 2008 at 2:20 pm
No, I don't want a SP to do it, I already have an SP to insert the records.
Here it some psuedo-code:
for each record in table1 where columnx is null
call procedure insert_record
October 31, 2008 at 2:21 pm
SELECT * FROM ptSchedule WHERE EncounterID IS NULL
would be the exact select query.
The exact SP call would be
exec rr_Insert_ptEncounter param1, param2, ...
October 31, 2008 at 2:24 pm
That's the problem then. If you've written your stored procedure to only insert one row at a time, you are stuck with RBAR unless you re-create the SP to work off of a set of data. If you think you need to keep your SP as-is, write a cursor (bad idea); otherwise, re-write your stored procedure to insert the entire set of data at once (good idea).
October 31, 2008 at 2:37 pm
Because the SP does other things along with inserting a record into a master table, we can't rewrite it. I was initially going to insert only via code but was told that I would have to do a lot of other stuff also in code, so, I can't do it with straight SQL update.
I don't know much about using cursors, either...
October 31, 2008 at 4:18 pm
Here is my initial try at using a cursor but I don't think it is going to work correctly.
I tried it selecting just 2 records into the cursor and it updated over 100 records
before I stopped it, whoa...
-- select scheduleid from ptschedule where encounterid is null
DECLARE @ScheduleID int
DECLARE @MRN1 varchar(25)
DECLARE @ResourceID1 int
DECLARE @StartDtTm1 datetime
DECLARE ptSchedule_cursor CURSOR FAST_FORWARD
FOR SELECT ScheduleID, MRN, ResourceID, StartDtTm FROM ptSchedule WHERE ScheduleID in (272174, 272175)
-- ScheduleId IS NULL AND StartDtTm > '10/20/2008'
OPEN ptSchedule_cursor
FETCH NEXT FROM ptSchedule_cursor INTO @ScheduleID, @MRN1, @ResourceID1, @StartDtTm1
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the next bill number
declare @p3 nvarchar(1000)
exec rr_Get_coPracticeSettings_Counter @Category=N'ENCOUNTERBILLNUMBER',@NumLength=0,@ID=@p3 output
-- Insert the Encounter
declare @p1 int
exec rr_Insert_ptEncounter @EncounterID=@p1 output
, @BillNumber=@p3
, @ClaimNumber=NULL
, @MRN=@MRN1
, @SubscriberContactID=0
, @LocationID=1
, @AuthID=0
, @BillerUserID=0
, @PostingDtTm=NULL
, @SiteID=@ResourceID1
, @DeptID=0
, @InsuranceID=0
, @RefPhysID=0
, @ProviderID=0
, @DateOfService=@StartDtTm1
, @DateOfIllness=NULL
, @TotalAmt=0
, @PaidAmt=0
, @OpenAmt=0
, @PaidInFull=0
, @ClaimSubmissionDtTm=NULL
, @ClaimStatus=0
, @Diag1=NULL
, @Diag2=NULL
, @Diag3=NULL
, @Diag4=NULL
, @StatementMessage1=NULL
, @StatementMessage2=NULL
, @BillComment1=NULL
, @BillComment2=NULL
, @AdmitDtTm=NULL
, @DischargeDtTm=NULL
, @AutoAccidentRelated=0
, @EmploymentAccidentRelated=0
, @OtherAccidentRelated=0
, @State=NULL
, @PreAuthNumber=NULL
, @AcceptAssignment=0
, @Errors=NULL
, @FirstOnsetDtTm=NULL
, @BucketID=0
, @CreatedByUserID=138
-- Update the ptSchedule EncounterID column
UPDATE ptSchedule SET EncounterID = @p1 WHERE ScheduleID = @ScheduleID
END
CLOSE ptSchedule_cursor
DEALLOCATE ptSchedule_cursor
November 2, 2008 at 12:24 pm
Anyone lurking around this weekend?
November 2, 2008 at 2:49 pm
I don't use cursors much, but it seems like you're missing a FETCH NEXT within your loop. You fetch the first set initially, and then begin your loop, but do not fetch again inside your loop before the END. Are the 100 rows updated displayed like:
1 row affected.
1 row affected.
1 row affected.
etc?
November 2, 2008 at 3:14 pm
Yea, Garadin is correct. You need a second FETCH statement in your while loop. Also, something doesn't look right to me here. Your code does an update at the end of the SP runs:
-- Update the ptSchedule EncounterID column
UPDATE ptSchedule SET EncounterID = @p1 WHERE ScheduleID = @ScheduleID
The problem I have with this is that your cursor will be written to run on rows where ScheduleID IS NULL. I realize that this test was run against static ScheduleID values, but I am assuming that the code line that you've commented out:
-- ScheduleId IS NULL AND StartDtTm > '10/20/2008'
is going to be how you want to run this in production? This update will not every run because your @ScheduleID variable will hold the a NULL value and you cannot do a equality comparison on a NULL value (NULL does not = NULL).
I would suggest that you post your SP code that you don't think can be re-written and let us take a look at it.
November 3, 2008 at 7:08 am
John Rowan (11/2/2008)
Yea, Garadin is correct. You need a second FETCH statement in your while loop.
I did forget that, didn't I?
John Rowan (11/2/2008)
The problem I have with this is that your cursor will be written to run on rows where ScheduleID IS NULL.
Can the cursor be written against a CTE and then the update happen on the regular table?
John Rowan (11/2/2008)
I would suggest that you post your SP code that you don't think can be re-written and let us take a look at it.
The SP has nothing to do with this and I do not need to post it.
November 3, 2008 at 7:17 am
Can the cursor be written against a CTE and then the update happen on the regular table?
The issue isn't the cursor itself, it's the fact that you've got NULL = NULL at the end. NULL doesn't = NULL, NULL IS NULL. The update statement at the end of your cursor just needs to be modified to use a different piece of criteria, other than the ScheduleID which won't exist.
The SP has nothing to do with this and I do not need to post it.
It has nothing to do with the question you're asking, the reason he was asking is he was looking to take a crack at re-writing the whole thing to not be a single line based insert in the first place (what you were originally looking at doing before you found out a whole lot more work would be involved ;)). That said, I've got some in my system that are probably 100,000+ lines of code (by the time you get through the ridiculous nesting levels and multi thousand line triggers) that I can't re-write for similar reasons, so I have an idea where you're coming from.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply