Find Affected Row Count from a Dynamic Query

Question

Post reply

Find Affected Row Count from a Dynamic Query

shamshudheen

SSCrazy

Points: 2390
More actions
April 17, 2008 at 11:18 pm

#71481

Dear Gurus,
Following is my procedure
CREATE PROCEDURE prg_SHOW_VIEW_RUNNINGTIME ( @strViewName NVARCHAR(255) )
AS
BEGIN
DECLARE @strSql NVARCHAR(400)
,@dtStartTimeDATETIME
,@dtEndTimeDATETIME
,@rowcountINT
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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

_____________
Code for TallyGenerator

shamshudheen SSCrazy Points: 2390 More actions · Answer 2

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 SSChampion Points: 13623 More actions · Answer 3

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 SSCrazy Points: 2390 More actions · Answer 4

i already tried the result is 1 not rows affected

try yourside

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 SSChampion Points: 13623 More actions · Answer 6

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 SSCrazy Points: 2390 More actions · Answer 7

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 SSC Enthusiast Points: 150 More actions · Answer 8

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 SSC Enthusiast Points: 149 More actions · Answer 9

Use

[Code]

Set NoCount ON

[/code]

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

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Output parameter gives more flexibility, as you do not need to have the counted SELECT as the last statement.

It even allows you to aggregate 2 or more counts:

DECLARE @sSQL NVARCHAR(500), @rowcount INT

SET @sSQL = 'select * from sysobjects;

SET @Count = @@ROWCOUNT;

select * from syscolumns;

SELECT @Count = @Count + @@ROWCOUNT'

EXEC sp_executesql @sSQL, N'@Count int OUTPUT', @Count = @rowcount OUTPUT

PRINT '@rowcount = ' + CAST(@rowcount AS VARCHAR(10))

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

danurbin (1/19/2015)
Use
[Code]
Set NoCount ON
[/code]
at the beginning of the script. for good measure, turn it back off when done...

Shouldn't need to turn it off. When the session closes, so does the setting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

prakashbala Grasshopper Points: 13 More actions · Answer 12

DECLARE @sqlBody VARCHAR(500),@TableCount INT, @SQL NVARCHAR(1000) SELECT @sqlBody = 'from sysobjects'SELECT @SQL = N'SELECT @TableCount = COUNT(*) ' + @sqlBody EXEC sp_executesql @SQL, N'@TableCount INT OUTPUT', @TableCount OUTPUTSELECT @TableCountGO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Please note: 3 year old thread

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