December 8, 2009 at 2:02 am
i know how much you lot hate your cursors 😉
unfortunately ive got into the habit of using them alot and wish to get out of it fast.
with the help of others on this forum i managed to replace my cursor in a dynamic pivot table with a temporary table, but i couldn't work out whether i could simply port that idea over to this next stored procedure.
if you have any links that you could point me to, to help me in my quest or show me the way yourselves, i would be extremely happy.
thanks
i start off with a select statment that brings back two columns. ID and Schema. the schema is held in a column of data type ntext.
DECLARE aCursor CURSOR FOR
SELECT e.ID,d.Schema From Evidence e INNER JOIN Documents d ON a.DocOne = d.ID
OPEN aCursor
FETCH NEXT FROM aCursor INTO @aID,@OrigSchema
i will then cursor through the results and make updates:
SELECT @Headertag = dbo.udf_extractInfo(@origSchema,'Header') --this will extract all information from the header tag
SELECT @AssessmentNumber = dbo.udf_extractInfo(@Headertag,'Assessment') -- this will extract the value from the assessment tag
IF @AssessmentNumber = 2
BEGIN
--update row
--update row
--update row
END
ELSE
IF @AssessmentNumber = 3
BEGIN
--update row
--update row
--update row
END
December 8, 2009 at 3:19 am
What about "@HeaderTag"?
What do you mean by "update row"?
You can use a "CASE" to update a column depending on a certain variable value. However, I need more info to suggest some code...
December 8, 2009 at 4:28 am
OK. here's a slightly updated version. as you can see from the stored procedures that do the update, all but one parameter is hardcoded.
the parameter that isn't hardcoded is retrieved from the cursor.
the SELECT statement is currently bringing back 83 rows but this could increase quite considerably.
DECLARE aCursor CURSOR FOR
SELECT e.ID,d.Schema From Evidence e INNER JOIN Documents d ON a.DocOne = d.ID
OPEN aCursor
FETCH NEXT FROM aCursor INTO @aID,@OrigSchema
i will then cursor through the results and make updates.
the @Headertag variable is being filled in the top line. the @Headertag is then being passed to the second line to be able to get the assessment number.
the reason why i'm splitting them up like this rather than going straight for the assessment number is so that i have smaller, more managable, subsets of data.
SELECT @Headertag = dbo.udf_extractInfo(@origSchema,'Header') --this will extract all information from the header tag
SELECT @AssessmentNumber = dbo.udf_extractInfo(@Headertag,'Assessment') -- this will extract the value from the assessment tag
IF @AssessmentNumber = 2
BEGIN
EXEC dbo.usp_StoredProcedure 1370,@aID,8003,0,0
EXEC dbo.usp_StoredProcedure 1374,@aID,8003,0,0
EXEC dbo.usp_StoredProcedure 1406,@aID,8003,0,0
END
ELSE
IF @AssessmentNumber = 3
BEGIN
Exec dbo.usp_StoredProcedure 1371,@aID,8003,0,0
Exec dbo.usp_StoredProcedure 1371,@aID,8003,0,0
Exec dbo.usp_StoredProcedure 1371,@aID,8003,0,0
END
December 8, 2009 at 4:52 am
The issue is, What does 'usp_StoredProcedure' do ?
Firstly you need to expose the code inside that/them to ensure that the code inside can work in a set based fashion.
December 8, 2009 at 5:40 am
sorry, i'm still getting used to what info is required, while at the same time not giving too much of our database structure away.
hopefully this is enough information to make any decision? thanks for your time.
the stored procedure is one that updates tables depending on what columnID you pass in.
due to a couple of tables we have, we can find the column name and table name that that column comes from, based on the columnID that is passed to it.
it then does a simple update statment
update @tablename set @columnname = @newValue where ID = @ID
this stored procedure cannot be changed, presuming that that is a prerequisite to any changes you may have been able to come up with.
December 8, 2009 at 5:56 am
Hi David
This implies that you can only update one column, of one row, per statement - is this correct or am I missing the point somewhere? If it is the case, and you are only able to update one column of one row at any one time, then have a look at this pseudocode statement and consider the possibilities:
UPDATE c SET Surname = q.Surname, Forename = q.Forename
FROM Customers c
INNER JOIN (SELECT CustomerID, Surname, Forename
"from absolutely anywhere to give a list of ID's which need to be updated"
WHERE whatever) q ON q.CustomerID = c.CustomerID
The derived table(s) can of course be used to generate aggregates, new row numbers, you name it....
It looks like you've got a hell of a limitation there?
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
December 8, 2009 at 5:58 am
davidandrews13 (12/8/2009)
sorry, i'm still getting used to what info is required, while at the same time not giving too much of our database structure away.hopefully this is enough information to make any decision? thanks for your time.
Sound to me like this is an EAV system , which , broadly speaking, are a bad idea for precisely the reason you are now encountering.
It would appear that you dont even know which table you are updating. Or the lifespan of the cursor will you be updating one / one hundred / or one thousand tables ? To craft an efficient sql routine will be tricky as the relationships between the data could change at any time.
I think you should start by attempting to select and 'PIVOT' out the data to be updated from you source data tables.
December 8, 2009 at 7:07 am
Chris Morris-439714 (12/8/2009)
Hi DavidThis implies that you can only update one column, of one row, per statement - is this correct or am I missing the point somewhere?
broadly speaking, yes.
although i could run the stored procedure multiple times, for each column i wanted to update, for each row that i'd be cursing through.
its basically an auditing stored procedure. it creates a record of the change in the audit table, as well as updating the record i want to update, so that there is always a history.
December 8, 2009 at 7:31 am
davidandrews13 (12/8/2009)
Chris Morris-439714 (12/8/2009)
Hi DavidThis implies that you can only update one column, of one row, per statement - is this correct or am I missing the point somewhere?
broadly speaking, yes.
although i could run the stored procedure multiple times, for each column i wanted to update, for each row that i'd be cursing through.
its basically an auditing stored procedure. it creates a record of the change in the audit table, as well as updating the record i want to update, so that there is always a history.
Do you have to use the stored procedure for all updates?
Has your DBA not heard of triggers?
Sorry, that probably doesn't help you. If you are indeed forced to update one row (and column) at a time via a stored procedure, then you may as well stick with the existing cursor method. The whole point of the anti-cursor argument is that cursors dictate, or at least encourage, table changes row by row rather than as a set. Looks like you don't have much choice.
Now, what if you came up with a set-based update which includes the code for the audit part, would you be permitted to put it into production? If it was hundreds of times faster than the original cursor / auditing sp version?
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
December 8, 2009 at 9:01 am
Chris Morris-439714 (12/8/2009)
davidandrews13 (12/8/2009)
Chris Morris-439714 (12/8/2009)
Hi DavidThis implies that you can only update one column, of one row, per statement - is this correct or am I missing the point somewhere?
broadly speaking, yes.
although i could run the stored procedure multiple times, for each column i wanted to update, for each row that i'd be cursing through.
its basically an auditing stored procedure. it creates a record of the change in the audit table, as well as updating the record i want to update, so that there is always a history.
Do you have to use the stored procedure for all updates?
Has your DBA not heard of triggers?
Sorry, that probably doesn't help you. If you are indeed forced to update one row (and column) at a time via a stored procedure, then you may as well stick with the existing cursor method. The whole point of the anti-cursor argument is that cursors dictate, or at least encourage, table changes row by row rather than as a set. Looks like you don't have much choice.
Now, what if you came up with a set-based update which includes the code for the audit part, would you be permitted to put it into production? If it was hundreds of times faster than the original cursor / auditing sp version?
i'll certainly have alook at doing that.
i know that our department isn't keen on triggers. i'm not certain as to why though.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply