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