Find Affected Row Count from a Dynamic Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Find Affected Row Count from a Dynamic Query

Find Affected Row Count from a Dynamic Query

Rate Topic

Display Mode

Topic Options

Author

Message

shamshudheen

Posted Thursday, April 17, 2008 11:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211

Dear Gurus,

Following is my procedure

CREATE PROCEDURE prg_SHOW_VIEW_RUNNINGTIME ( @strViewName NVARCHAR(255) )
AS
BEGIN
DECLARE @strSql NVARCHAR(400)
,@dtStartTime DATETIME
,@dtEndTime DATETIME
,@rowcount INT

SET @dtStartTime = GETDATE()

SET @strSql = 'SELECT * FROM ' + @strViewName

EXEC @rowcount = sp_executesql @strSql

SET @dtEndTime = GETDATE()

SELECT @rowcount AS ROWS_AFFECTED
SELECT DATEDIFF(s,@dtStartTime,@dtEndTime) / 60 AS EXECUTION_TIME

END

from this @rowcount variable returning 0 value, but i want it should return the rows affected by the dynamic query

any help is highly appreciated

Thanks in Advance

Post #486903

Sergiy

Posted Thursday, April 17, 2008 11:51 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 4,557, Visits: 8,236

You need to pass @rowcount as na OUTPUT parameter to sp_executesql.

Post #486909

shamshudheen

Posted Friday, April 18, 2008 5:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211

Sergiy (4/17/2008)

You need to pass @rowcount as na OUTPUT parameter to sp_executesql.

i dont get can you alter my query and show me

thanks in advance

Post #487078

RyanRandall

Posted Friday, April 18, 2008 5:58 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

This might be simpler...

Replace this...

EXEC @rowcount = sp_executesql @strSql

with this...

EXEC sp_executesql @strSql
set @rowcount = @@RowCount

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Post #487103

shamshudheen

Posted Friday, April 18, 2008 6:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211

i already tried the result is 1 not rows affected

try yourside

Post #487116

GilaMonster

Posted Friday, April 18, 2008 6:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 38,069, Visits: 30,363

I did.

DECLARE @sSQL NVARCHAR(100), @rowcount INT

SET @sSQL = 'select * from sysobjects'
EXEC sp_executesql @sSQL
SELECT @rowcount = @@ROWCOUNT
PRINT '@rowcount = ' + CAST(@rowcount AS VARCHAR(4))

Output in the messages tab -

(118 row(s) affected)
@rowcount = 118

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #487119

RyanRandall

Posted Friday, April 18, 2008 6:18 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

GilaMonster (4/18/2008)

I did.

DECLARE @sSQL NVARCHAR(100), @rowcount INT

SET @sSQL = 'select * from sysobjects'
EXEC sp_executesql @sSQL
SELECT @rowcount = @@ROWCOUNT
PRINT '@rowcount = ' + CAST(@rowcount AS VARCHAR(4))

Output in the messages tab -

(118 row(s) affected)
@rowcount = 118

So did I! Thanks Gail.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Post #487122

shamshudheen

Posted Monday, April 21, 2008 12:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211

shamshudheen (4/18/2008)

i already tried the result is 1 not rows affected

try yourside

Hi guys,

I am sorry, i made a mistake after execute sql i called getdate() function and after i called @@rowcount, so that i got wrong result

but your methods are fine

thank you all

Post #487718

abhi.guru

Posted Tuesday, December 20, 2011 3:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:03 AM
Points: 9, Visits: 91

Hello Friends,

I know this is an old topic but related to my issue.

I have a similiar requirement. How do I suppress the output of this sp_executesql.?

if we run the below sql statments, we get all the data relating to sysobjects.

DECLARE @sSQL NVARCHAR(100), @rowcount INT

SET @sSQL = 'select * from sysobjects'
EXEC sp_executesql @sSQL
SELECT @rowcount = @@ROWCOUNT
PRINT '@rowcount = ' + CAST(@rowcount AS VARCHAR(4))

but I am just interested in knowing only whether the select query is valid or not. I tried parseonly but the sql statments which I am running is inside a stored procedure (a sqlserver stored procedure cannot use parseonly command).

Help

Abhi

Post #1224795

« Prev Topic | Next Topic »

Permissions