MS SQL cursors

Hello, Aurimas Lacitis!
Do you need print results only?

No, this is just an example.
I do some inserts, updates or call other procedures inside of the loop using values of the cursor, it depends on task I am doing.
Of course, where possible, I use Insert As Select or Update From Select statements, but sometimes I need to use cursors.
When cursor has two columns it is ok, but when 20, I have to declare 20 variables for them :), so I am looking for some simpler way of using cursor, like for example in Oracle.

Unfortunatly, t-sql can't do so
I have some advices:
1. Don't use cursors :) They are slow. Instead of them i try to use insert, update statement with cross or cartesian product
2. Try to use sp_MSforeachtable. Read. Some times it helps.
3. And... declare variables. In my opinion it is not hard, much harder to write correct logic in cursor

Thank You, Александр, :)

Александр, I have a bit different question :), maybe You will know is it possible?
When procedure executes update table ... and receives an error (for example unique constraint failed or etc.), is it possible to determine, which record has been being updated when error occured, I mean a value of primary key or other identification?
On error I can get Error_number(), Error_message(), etc., maybe there some other system functions or variables, which might return the exact record's ID?

No, it's impossible. This error can occur if more than one record refers to a deleted and even difficult to understand what the table was the cause of the error does not mention what the records :)

Yes, You are right, there can be many such records, but still there is always the first record in the queue which cause the error first and database internal cursors and mechanisms might know that :)

Aurimas, you can use instead of triggers to detect/log errorneous records. However it will slow down the operations depending on the logic you have to implement.

Валентин, how do triggers help? could you make example?
Thanks

Hello, Alexander.
Some example how instead of trigger can help to catch rows with incorrect values:

CREATE TABLE tbl_Test (
	Name NVARCHAR(250) PRIMARY KEY,
	Description NVARCHAR(250) NOT NULL
)
 
GO
 
CREATE TRIGGER tr_tbl_Test_II
   ON  tbl_Test
   INSTEAD OF INSERT
AS 
BEGIN	
    SET NOCOUNT ON;
 
    DECLARE @BadRecords NVARCHAR(4000)
 
    SELECT @BadRecords = ISNULL(@BadRecords + ', ', '') + I.Name
    FROM INSERTED I
    WHERE Description IS NULL
 
    IF @BadRecords IS NULL
		INSERT INTO tbl_Test
		SELECT * FROM INSERTED
	ELSE
		PRINT 'You cannot insert null into field Description. Records are incorrect, that have Name = ' + @BadRecords
END
GO
 
 
INSERT INTO tbl_Test (Name, Description)
SELECT '1', '1'
UNION 
SELECT '2', NULL
UNION 
SELECT '3', NULL

Of course, this is very particular solution.

Артем, read carefully this
How does your solution help?

Артём, yes, just a little bit different. It is well, but in addition we may want to analyze the result of the delete query and sometimes we will have very complicated algorithms to check the items affected by update/delete queries (as stated by Александр above).
Also, I should say, your example is very fast and may satisfy the Aurimas' tasks. Isn't it so? Thank you for good idea!

Александр, Aurimas, I've meant the following:
1. Using special table for fixing the items being processed (like rep_Delete used for the internal replications purposes).
2. Fetching the items from updated/deleted tables one by one within the trigger till the error occurs.
3. Checking the items have been processed successfully.
Some variations and ideas can be used for these general steps can make this decision aplicable for your certain tasks.
As a result, you will have the ability to find the record can't be updated/deleted. And to take the appropriate action. You will not need the special algorithm for each table dependent on it's structure or relations. But I should state that it will slow down a little bit this tables. So, the decision should be taken concerning this circumstance.
I'm lazy a bit to provide the example, but if you will insist... ;)
Thank you!

"Осауленко Александр" написал:
Артем, read carefully this
How does your solution help?

Alexander, I've read this carefully. I think, the task is to determine records, which are incorrect in insert/update/delete operation with exact table. In my example I only checked one constraint in one operation and showed the result like print message. Of course, this is very simple example, and, as Valentin said, you may need some log table, where you can find after insert/update/delete operation all information about the operation error, including IDs of incorrect records as Aurimas asked.

Артем and Валентин, you forgot a small in first sight but very important thing - how user who hasn't right to records will change them if you return ids record? I even don't tell that your solution is very slow and hard to support.

"Осауленко Александр" написал:how user who hasn't right to records will change them if you return ids record?

The situation, when this solution would be useful: regular/online import from some system via several stored procedures or directly into table. Admin should know how import is going and, if there are some errors, wants to know which exactly rows where (or tried to be) imported incorrectly.

"Осауленко Александр" написал:I even don't tell that your solution is very slow and hard to support.

Hello, Thank You, colleagues,

Don't You think cursors will be faster in this case than using Instead Of triggers to determine bad records? I should try that :)

If I understood example well, if a table has more constraints, for example, unique keys, check constraints, all this logic have to be implemented in Instead Of trigger as well.

"Репко Артём" написал:About rights there wasn't any word.

"Aurimas Lacitis" написал:If I understood example well, if a table has more constraints, for example, unique keys, check constraints, all this logic have to be implemented in Instead Of trigger as well.

Access rights is not a problem in this case :)

Thank You all for help!