Need to retrieve number of rows affected by SQL query

[Former Member]

Hi,

I am executing some update queries in SQL in a xMII transaction. Iam using a SQL Query template for the same and I need to find out how many records were affected by the last executed statement. I have tried doing SELECT @@ROWCOUNT in a second SQL template that executes after the UPDATE SQL Query Template. However, the results are intermittent, sometimes it returns 1 row affected as expected but sometimes returns 0 rows. Can anyone help on this.

Thanks

[Former Member · Accepted solution]

Actually, my point is I do not have authorisation to modify table structures or create new ones. I understand this option might work for me, but it involves table/triggger creation. Is there any other way to resolve this?

[sidnooradarsh · Accepted solution]

Anamika,

1) As per sql best practices use of trigger is not recommend unless you don't have any other option because usually triggers consume more resources and IO since it involves internal monitoring.

2) @@ROWCOUNT should go in same SQL session where you are performing update to identify rows affected else as you said earlier your results will not be accurate

3) The other alternative is to create a Stored Procedure but it again boils down to your authorization issue. This would be easier and better if you have authorization to create SP's. But nevertheless give it a try.

4) Lets forget about SP say that you have an Update Statement like

Update Table1 set column2 = 'abc' where Column2 = 'xyz'

In this case it is evident that you know what you are passing into the Where clause and to what table hence its obvious that you also know how many rows will be affected by using query,

 Select count(*) from Table1 where Column2 = 'abc' 

The @@ROWCOUNT is powerful within t-sql with SP, cursors etc..

Hope this helps!!

Regards,

Adarsh

[Former Member · Accepted solution]

Thanks Adarsh,

Since @@ROWCOUNT doesnt work for me, Iam now using SELECT COUNT(col1) ... approach.. This has resolved the problem iam facing. But its still a mystery for me why @@ROWCOUNT doesnt work sometimes..

[sidnooradarsh · Accepted solution]

Glad your problem got solved.

@@ROWCOUNT will not work because when you put @@ROWCOUNT in different Query Template the @@ROWCOUNT doesn't know which table was updated/inserted and it will show you the last/latest affected operation which could be anything other than your update statement hence its inaccurate.

But if you place your @@ROWCOUNT immediately after any update/insert then it is nothing but putting all statements within a single transaction or single user session which should definitely give you results.

Do a small test in sql analyzer you will get to know

Good Luck!!

Regards,

Adarsh

[Former Member · Accepted solution]

Thanks..I know it works in SQL analyzer..but bad part is XMII doesnt support such things..

[jcgood25 · Accepted solution]

Try using an alias for the column:

SELECT @@ROWCOUNT AS NumRows.....

[Former Member · Accepted solution]

U can try using OUTPUT clause along with your update query within Query template itself.

You can refer this link for more details on OUTPUT clause

http://technet.microsoft.com/en-us/library/ms177564.aspx

Your query mode should be set to Query and not Command.

Regards,

Shalaka

[Former Member]

@Som,

I do not have any control over the database end, hence cannot create new tables/triggers.

@Adarsh,

I tried adding the @@Rowcount query at the end of the update statement.

E.g. I modified my query from Update mytable set col1='Some value' where col2=1234

Update mytable set col1='Some value' where col2=1234

to

Update mytable set col1='Some value' where col2=1234 ; Select @@RowCount

However this doesnt return any useful message. It simply returns Command Query Successful and not the count of rows affected.

[former_member201407]

You don't need to have control over DB end or go into the same, instead you can create table/trigger from MII end using the mode = Command in SQL Query.

Regards

Som

[sidnooradarsh]

Hello,

Instead of using separate Query template for @@ROWCOUNT.

Put it as the last statement in the same Query after the update statement.

Regards,

Adarsh

[former_member201407]

You can try using TRIGGER in another table where number of Audits will be inserted on time of execution of Update of the desired table

Regards

Som