May 8, 2012 at 10:21 am
Hi,
In Oracle, we use SET SERVEROUTPUT ON to display the row count and SQL%ROWCOUNT to get actual no.of rows updated/Inserted/deleted by boclk of pl-sql code
For example, I run the below pl-sql code from sqlplus as below:
save the pl-sql code to a .sql file, update.sql
from sqlplus, connect to oracle database and execute the file update.sql
If the row count is correct, then issue COMMIT otherwise issue ROLLBACK.
SET SERVEROUTPUT ON;
DECLARE
v_count NUMBER := 0;
BEGIN
UPDATE staff
SET function_role =
(SELECT function_role
FROM function_role
WHERE functional_role_name = 'CLR')
WHERE UPPER (first_name) = UPPER ('scott')
AND UPPER (last_name) = UPPER ('Harrison');
v_count := v_count + SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Updated ' || v_count || ' rows in STAFF Table');
END;
/
How to achive the same in SQL Server?
Thanks
May 8, 2012 at 10:28 am
I think @@ROWCOUNT is what you're after.
http://technet.microsoft.com/en-us/library/ms187316(v=sql.105).aspx
As in, SET v_count = @@ROWCOUNT.
Cheers
May 8, 2012 at 12:03 pm
below is the pl-sql code.
It, displays the number of total records inserted/updated/deleted.
If the count matches, will issue COMMIT otherwise rollback.
SET SERVEROUTPUT ON;
DECLARE
v_update number := 0;
v_delete number := 0;
v_insert number := 0;
Begin
delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;
v_delete := v_delete + SQL%ROWCOUNT;
update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;
update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;
update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;
v_update := v_update + SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('ROWS UPDATED ' || v_update);
DBMS_OUTPUT.PUT_LINE('ROWS DELETED ' || v_delete);
DBMS_OUTPUT.PUT_LINE('ROWS INSERTED ' || v_insert);
END;
/
What is the equivalent code in t-sql to achieve the same as above?
May 8, 2012 at 12:08 pm
You were told to use the @@ROWCOUNT function. Here is how you would use it:
delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;
SET @v_delete = @@ROWCOUNT;
update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;
SET @v_update = @@ROWCOUNT;
update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;
SET @v_update = @v_update + @@ROWCOUNT;
update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;
SET @v_update = @v_update + @@ROWCOUNT;
Not saying the code above will work in SQL Server as the only parts I modified were where @@ROWCOUNT is used.
May 8, 2012 at 12:09 pm
Something like this?
declare @Delete int, @Update int, @Insert int
delete from bill_detail where bill_detail in (9878506)
and policy = 4236807;
select @Delete = @@ROWCOUNT
update prem set written_prem = 50,
commission_amt = 7.50,
audit_id = USER,
last_modified = SYSDATE
where prem in (85272831)
and policy = 3567140;
select @Update = @@ROWCOUNT
update bill_detail set gross_amt = 50,
commission_amt = 7.50,
net_amt = 42.5
where bill_detail = 9881358 and policy = 3567140;
select @Update = @Update + @@ROWCOUNT
update installment set remaining_prem = 50,
installment_status = 'Future',
-- original_prem = 501,
total_due = 50,
paid_date = NULL,
bill_date = NULL
where installment = 21820355
and policy = 3567140;
select @Update = @Update + @@ROWCOUNT
print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 12:36 pm
Sean,
I executed the below code as you suggested and I'm getting the below results in SSMS
declare @Delete int, @Update int, @Insert int
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT
print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'
(0 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
1 Row(s) Updated
1 Row(s) Deleted
1 Row(s) Inserted
But looking at the results, row(s) affected and the total count, it's not matching.
And I want to rollback it and correct the t-sql. Please advise.
May 8, 2012 at 12:43 pm
Did you put this inside a transaction? If so, just "ROLLBACK TRANSACTION"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2012 at 12:44 pm
madhu-686862 (5/8/2012)
Sean,I executed the below code as you suggested and I'm getting the below results in SSMS
declare @Delete int, @Update int, @Insert int
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT
print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'
(0 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
1 Row(s) Updated
1 Row(s) Deleted
1 Row(s) Inserted
But looking at the results, row(s) affected and the total count, it's not matching.
And I want to rollback it and correct the t-sql. Please advise.
declare @Delete int, @Update int, @Insert int
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'CopyRequested'
select @Delete = @@ROWCOUNT <<<< Sets @Delete to number of rows deleted
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'AdditionalIntrestType'
select @Delete = @@ROWCOUNT <<<<< Sets @Delete to number of rows deleted, previous value lost
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','entity','Entity')
select @insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('AdditionalIntrestType','individual','Individual')
select @insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted, previous value lost
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 'DirectBill'
,[VALUE_MTD] = 'Direct Bill'
WHERE [ETYPE_EWT] = 'TypesOfBill' and [KEY_MTD] = 'Direct'
select @update = @@ROWCOUNT <<<< Sets @updateto number of rows updated
UPDATE [dbo].[DATA_EWT]
SET [KEY_MTD] = 10
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Completed'
select @Update = @@ROWCOUNT <<<< Sets @updateto number of rows updated, previous value lost
DELETE FROM [dbo].[DATA_EWT]
WHERE [ETYPE_EWT] = 'ApplicationStatus' AND [VALUE_MTD] = 'Application Declined'
select @Delete = @@ROWCOUNT <<<< Sets @Deleteto number of rows deleted, previous value lost
INSERT INTO [dbo].[DATA_EWT]
([ETYPE_EWT]
,[KEY_MTD]
,[VALUE_MTD])
VALUES
('ApplicationStatus'
,11
,'Application Declined')
select @Insert = @@ROWCOUNT <<<< Sets @insert to number of rows inserted, previous value lost
print cast(@Update as varchar(6)) + ' Row(s) Updated'
print cast(@Delete as varchar(6)) + ' Row(s) Deleted'
print cast(@Insert as varchar(6)) + ' Row(s) Inserted'
Did you happen to look at some of what I put in my post above? You know, SET @update = @update + @@ROWCOUNT; (for example)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy