September 19, 2012 at 1:11 pm
It is possible to get the number of rows affected in a multiple statement transaction without adding the @@rowcount to a parameter for each statement?
Jared
CE - Microsoft
September 19, 2012 at 1:18 pm
SQLKnowItAll (9/19/2012)
It is possible to get the number of rows affected in a multiple statement transaction without adding the @@rowcount to a parameter for each statement?
As far as I know the answer is no. @@rowcount is granular to the last statement executed and I do not remember any counter with transaction granularity.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2012 at 1:22 pm
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))
GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
Jared
CE - Microsoft
September 19, 2012 at 1:22 pm
PaulB-TheOneAndOnly (9/19/2012)
SQLKnowItAll (9/19/2012)
It is possible to get the number of rows affected in a multiple statement transaction without adding the @@rowcount to a parameter for each statement?As far as I know the answer is no. @@rowcount is granular to the last statement executed and I do not remember any counter with transaction granularity.
That's what I thought... Thanks Paul.
Jared
CE - Microsoft
September 19, 2012 at 1:50 pm
SQLKnowItAll (9/19/2012)
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
@@rowcount is correctly zero because your exec did not affect any rows. What I mean is the call to EXEC did not affect any rows. The scope of your dynamic sql is not visible to the current batch. You would have to have another mechanism to get your rowcount. You are probably correct that CLR might be a better alternative here.
_______________________________________________________________
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/
September 19, 2012 at 2:25 pm
Sean Lange (9/19/2012)
SQLKnowItAll (9/19/2012)
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
@@rowcount is correctly zero because your exec did not affect any rows. What I mean is the call to EXEC did not affect any rows. The scope of your dynamic sql is not visible to the current batch. You would have to have another mechanism to get your rowcount. You are probably correct that CLR might be a better alternative here.
Thanks Sean. Yeah, I knew the 0 was correct, but it would be cool to have something like an @@tranrowcount or something. Best handled outside of SQL anyway. Thanks!
Jared
CE - Microsoft
September 19, 2012 at 3:04 pm
Sean Lange (9/19/2012)
SQLKnowItAll (9/19/2012)
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
@@rowcount is correctly zero because your exec did not affect any rows. What I mean is the call to EXEC did not affect any rows. The scope of your dynamic sql is not visible to the current batch. You would have to have another mechanism to get your rowcount. You are probably correct that CLR might be a better alternative here.
@@ROWCOUNT returns 0 because the COMMIT does not affect rows per se.
If, for example, a SELECT or INSERT was the last statement in the EXEC(), @@ROWCOUNT would show you how many rows were SELECTed or INSERTed by the EXEC().
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 3:08 pm
ScottPletcher (9/19/2012)
Sean Lange (9/19/2012)
SQLKnowItAll (9/19/2012)
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
@@rowcount is correctly zero because your exec did not affect any rows. What I mean is the call to EXEC did not affect any rows. The scope of your dynamic sql is not visible to the current batch. You would have to have another mechanism to get your rowcount. You are probably correct that CLR might be a better alternative here.
@@ROWCOUNT returns 0 because the COMMIT does not affect rows per se.
If, for example, a SELECT or INSERT was the last statement in the EXEC(), @@ROWCOUNT would show you how many rows were SELECTed or INSERTed by the EXEC().
Thanks for the correction Scott. This is easy enough prove. Just take out the transaction from the posted code.
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))
GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'SELECT * FROM #test SELECT * FROM #test'
EXEC(@sql)
SELECT @@rowcount as MyCount
GO
_______________________________________________________________
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/
September 19, 2012 at 3:13 pm
SQLKnowItAll (9/19/2012)
Here's some sample code:
CREATE TABLE #test(id int identity (1,1), filler VARCHAR(1))GO
INSERT INTO #test(filler)
SELECT 'a'
GO 5
DECLARE @sql VARCHAR(150)
SET @sql = 'BEGIN TRAN SELECT * FROM #test SELECT * FROM #test COMMIT'
EXEC(@sql)
SELECT @@rowcount
GO
Right now, the result from SELECT @@rowcount is 0... However, what I would like is to get a result of 10 in this this case using something other than @@rowcount and without adding a parameter to the dynamic sql in between the 2 select statements. Can it be done or is there a place to get this information for the transaction? Any thoughts? We are probably trying to do something that should be done in CLR vs SQL, but maybe somebody has an idea here.
You could use sp_executesql and return the total of the @@ROWCOUNTs.
DECLARE @sql NVARCHAR(500)
DECLARE @rowcount int
SET @sql = 'SET @rowcount = 0 BEGIN TRAN SELECT * FROM #test SET @rowcount = @rowcount + @@ROWCOUNT SELECT * FROM #test SET @rowcount = @rowcount + @@ROWCOUNT COMMIT'
EXEC sp_executesql @sql, N'@rowcount int OUTPUT', @rowcount OUTPUT
PRINT @rowcount
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 3:19 pm
If you want/need EXEC() (vs sp_executesql), then you can use a temp table to return the row total:
DECLARE @sql VARCHAR(2000)
CREATE TABLE #result ( total_rows int )
SET @sql = 'DECLARE @rowcount int SET @rowcount = 0 BEGIN TRAN SELECT * FROM #test SET @rowcount = @rowcount + @@ROWCOUNT SELECT * FROM #test SET @rowcount = @rowcount + @@ROWCOUNT COMMIT INSERT INTO #result VALUES(@rowcount)'
EXEC (@sql)
DECLARE @rowcount int
SELECT @rowcount = total_rows FROM #result
PRINT @rowcount
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 3:42 pm
The business case is to allow certain groups of people to do their own data fixes (I know, that sounds scary). So, we want them to place their script as a parameter to a stored proc that places the script in a table, checks it for "bad" commands, and then executes it after an approval process. We would like to count the number of rows it affects as part of the pre-approval data. However, sometimes people place more than 1 statement as part of their transaction. We don't want to have to try to split their script and insert @someParam + @@rowcount. We would like to leave their code original and only do checks on it to see if it is acceptable to execute or not.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply