Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Affected Row Count from a Dynamic Query Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 11:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, April 17, 2008 11:51 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
You need to pass @rowcount as na OUTPUT parameter to sp_executesql.
Post #486909
Posted Friday, April 18, 2008 5:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, April 18, 2008 5:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Friday, April 18, 2008 6:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, April 18, 2008 6:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Friday, April 18, 2008 6:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Monday, April 21, 2008 12:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, December 20, 2011 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 1:23 PM
Points: 9, Visits: 114
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 »

Add to briefcase

Permissions Expand / Collapse