Select @@RowCount in dynamic sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select @@RowCount in dynamic sql

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-23 : 09:39:39

hi, how to retrieve row count from an insert statement in dynamic sql?



CREATE PROCEDURE [dbo].[procTest]	
AS
BEGIN
 SET NOCOUNT ON;

DECLARE @nsql NVARCHAR(MAX)
DECLARE @i INT
SET @nsql = 'select * into tbl_user_test from tbl_user; 
select @i = @@rowcount;'
exec sp_executesql @nsql;
END

The code doesn't work..


declare @i int;
Exec procTest, N'@i int output',@i=@i output
select @i;

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-23 : 09:46:20

here is one way:


CREATE PROCEDURE [dbo].[procTest]	
AS
BEGIN
 SET NOCOUNT ON;

DECLARE @nsql NVARCHAR(500)
DECLARE @i INT
SET @nsql = 'select * into tbl_user_test from tbl_user'
exec sp_executesql @nsql;
select @i = @@rowcount
select @i [@@rowcount]
END

EDIT:
Here is another:


CREATE PROCEDURE [dbo].[procTest]	
AS
BEGIN
 SET NOCOUNT ON;

DECLARE @nsql NVARCHAR(500)
DECLARE @i INT
SET @nsql = 'select * into #t1 from sysusers; select @i = @@rowcount'
exec sp_executesql @nsql, N'@i int output', @i=@i output
select @i [@@rowcount]
END

Be One with the Optimizer
TG

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-27 : 20:22:07

Is there any time consume issue when execute the select @@rowcount statement when the table size is huge?

SET @nsql = 'select * into #t1 from sysusers; select @i = @@rowcount'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-27 : 21:55:46

no, the rowcount global variable has already been set by running the actual statement. Selecting the @@rowcount value afterwards is negligible.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources