Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Affected Row Count from a Dynamic Query


Find Affected Row Count from a Dynamic Query

Author
Message
shamshudheen
shamshudheen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
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
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5828 Visits: 11397
You need to pass @rowcount as na OUTPUT parameter to sp_executesql.
shamshudheen
shamshudheen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
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
RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652
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.
shamshudheen
shamshudheen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 211
i already tried the result is 1 not rows affected

try yourside
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47200 Visits: 44367
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, MVP, M.Sc (Comp Sci)
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


RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652
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.
shamshudheen
shamshudheen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
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
abhi.guru
abhi.guru
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 123
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
danurbin
danurbin
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 26
Use

Set NoCount ON



at the beginning of the script. for good measure, turn it back off when done...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search