January 28, 2016 at 1:13 pm
I have a temp table inside a stored procedure. When I run @@rowcount after executing SP, it always returns 0.
However, if I removed Drop Table command, all worked fine.
Using AdventureWorks2012 database, here is my code:
CREATE PROCEDURE [dbo].[GeoCode]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tbEmployee') IS NOT NULL
DROP TABLE #tbEmployee
Create Table #tbEmployee (empId int, firstname varchar (100), lastname varchar (100) )
Insert into #tbEmployee
SELECT top 100 [BusinessEntityID],[FirstName],[LastName]
FROM[AdventureWorks2012].[Person].[Person]
SELECT [StateProvinceID],[StateProvinceCode],[Name]
FROM[AdventureWorks2012].[Person].[StateProvince]
Drop Table #tbEmployee
END
exec [dbo].[GeoCode]
select @@ROWCOUNT
if "Drop Table #tbEmployee" command in SP is commented out, then @@ROWCOUNT returns row count, otherwise, it returns 0.
My question is how can I drop the temp table without affecting @@ROWCOUNT?
January 28, 2016 at 1:18 pm
You can't. @@Rowcount is always the number of rows affected by the previous statement. DROP TABLE affects no rows, so sets @@rowcount to 0.
You could use an output variable to return it. Set the variable to @@RowCount after the statement who's rowcount you want to check
That said, you don't need to explicitly drop the temp table (nor check for its existence at the start). A temp table created in a procedure is automatically dropped when the procedure finishes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2016 at 1:41 pm
Thank you very much, Gail.
January 28, 2016 at 3:03 pm
You should always output results from procedures and use them specially if it it something like RowCount or a result of particular sql statement within encapsulated code (stored procedure), else you always have the risk of your code breaking if someone else happens to modify or extend stored procedure.
++ Gail
January 30, 2016 at 9:53 am
If you want a specific value, return it from the proc, don't rely on the "last" statement in the proc yielding the correct @@ROWCOUNT: how is someone later supposed to know they can't add lines to that proc??
To do it properly, you should use an OUTPUT parameter. However, in a hurry you might use the return code from the proc itself. Be sure then to return negative values for errors.
...
SELECT [StateProvinceID],[StateProvinceCode],[Name]
FROM[AdventureWorks2012].[Person].[StateProvince]
SET @rowcount = @@ROWCOUNT
Drop Table #tbEmployee
RETURN @rowcount
EXEC @rowcount = ...
--@rowcount now contains the row count from the desired SELECT in the proc
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".
Viewing 5 posts - 1 through 5 (of 5 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