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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply