Row Count of Insert stmt with sp

Question

Post reply

Row Count of Insert stmt with sp

Jonas Pranckevicius

SSC Veteran

Points: 277
More actions
November 29, 2004 at 6:18 am

#63572

Hi,
I need a rowcount of each executed statement.
I have this statement in while loop that reads some tables based on user input and populates temp table with results:
INSERT INTO tmpCDR_Hold_Table Execute sp_executesql @resQuery
IF @@ROWCOUNT = 0
Print 'Warning! No records where found in table ' + @strTableName + ' Continue running on the next table... '

The INSERT part is working fine, but @@ROWCOUNT part always returns 0. Why? I am thinking that it is because that sp_executesql doesn't return rowcount value. Is there an easy fix or maybe another way of doing this?
Note: That query statement is constructed dynamically based on user input (e.g. date ranges, table name, etc.), so I have to use sp_executesql or EXEC(@reQuery) to run it.
Appreciate any help. Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Based on your post

@resQuery must be a select statement

Following the INSERT @@ROWCOUNT will contain the number of rows inserted

When you say that it is always zero, how do you know, is the message from the print statement is always shown? Are you sure that @resQuery is returning rows?

Be aware that IF affects @@ROWCOUNT, if you want to retain the value that set an int variable to @@ROWCOUNT

Far away is close at hand in the images of elsewhere.
Anon.

Jonas Pranckevicius SSC Veteran Points: 277 More actions · Answer 2

David,

INSERT INTO tmpCDR_Hold_Table Exec( @resQuery) SET @rCounter = @@Rowcount

It worked. Thanks a lot. That IF statement was messing around, I didn't know that it affects.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 3

Hi Jonas, David,

I don't see why it isnt working either but one easy workaround would be to do some form of a count within the dynamic SQL and return both:-

Execute sp_executesql @resQuery, @RowCount

That way you wouldn't have to rely on @@ROWCOUNT.

Not ideal but...............

Have fun

Steve

We need men who can dream of things that never were.

Jonas Pranckevicius SSC Veteran Points: 277 More actions · Answer 4

You mean:

INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery, @@Rowcount, @@error

sp_executesql only takes sp_executesql [@stmt =] stmt

However this works: INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery SET @rCounter = @@Rowcount SET @errCode = @@Error

But only for SET @rCounter = @@Rowcount, it doesn't store error code, but if I switch the two it stores error code but not row count!

Does anyone know work arround, so that both variables would be stored?

Thanks

Jonas Pranckevicius SSC Veteran Points: 277 More actions · Answer 5

I found the answer myself. Here is to everyone to share:

INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery SELECT @errCode = @@Error, @rCounter = @@Rowcount

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 6

Hi Jonas,

sp_executesql only takes sp_executesql [@stmt =] stmt

You need to read a little more on the use of this sproc matey:-

SET @sql = N'SET @TableID = (SELECT TPK FROM Tables WHERE TableName = ''' + @TableName + ''')'

EXEC sp_executesql @sql, N'@TableID int OUTPUT', @TableID OUTPUT

Its one of the only ways to get one or more output parameters when using EXEC within a sproc.

Best regards

Steve

We need men who can dream of things that never were.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 7

Jonas,

Looks like we where both typing at the same time.......

Have fun

Steve

We need men who can dream of things that never were.

Jonas Pranckevicius SSC Veteran Points: 277 More actions · Answer 8

Thank You. Yes you are right I just newbie. I should have checked sproc myslef not on online books.