SQL Clone
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-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24437 Visits: 12464
You need to pass @rowcount as na OUTPUT parameter to sp_executesql.
shamshudheen
shamshudheen
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4075 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-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

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

try yourside
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213125 Visits: 46259
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4075 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-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 27
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