capture # of rows affected - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

capture # of rows affected

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-23 : 23:11:29

I have a stored procedure that runs a select command, how would i capture the qty of rows affected from that? for example, the query is simply:

 select top 100 per1_lname from mytable

how can i capture that i pulled 100 records? the code is just an example, so dont come back with "Well you selected the top 100 dummy!" :o)

also, fyi, basically my goal is to execute a stored procedure, and capture how many records the results pulled, then take that qty and update a record in another table

ex:

 
select per1_lname from mytable
go

update t1
set qtycaptured=qtycaptured+rowsaffected
from myquotatable t1
where username=@userpullingdata
go

Make Sense?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-23 : 23:14:51

select @@rowcount

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-23 : 23:15:39

WOW that was fast, how would i use that as far as syntex above?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-23 : 23:34:00

nevermind! i figured it out :o)


use test
DECLARE @find varchar(30)

select top 100* from condnc
set @find= @@rowcount

update t1
set qtyat=qtyat+@find
from quota t1 where username='username'

Thanks for all the help!!!

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-23 : 23:47:56

k, another quick one...

i need to get the quota from the table so that i can see what the user is allowed. i tried:


declare @quota numeric(18,0)
set @quota= select quota from quota where company='companyname'

but i got errors

ne guidence?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 23:53:56

[code]~~set @quota=~~ select @quota=quota from quota where company='companyname'[/code]

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-24 : 00:00:05

set @quota=(select quota from quota where company='companyname')

also seems to work, are they interchangable? or would your method be best?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 00:02:25

yes if you only have 1 variable.

my method allows for situation where there are more than 1 variables

select @quota=quota, @var2 = col2, @var3 = col3 from quota where company='companyname'

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-24 : 00:15:15

:o) another one... again.

how can i do a multi-line if? the way i use it now seems like you can only put one line after the if:

ex:


if @qtyat+@find<@quota
	update t1 set qtyat=qtyat+@find	from quota t1 where company='companyname'

if i put anything after the update (on another line) it seems to execute it weather the if is true or false. how can i tell sql i'm not done with the if statement yet?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-24 : 00:15:15

:o) another one... again.

how can i do a multi-line if? the way i use it now seems like you can only put one line after the if:

ex:


if @qtyat+@find<@quota
	update t1 set qtyat=qtyat+@find	from quota t1 where company='companyname'

if i put anything after the update (on another line) it seems to execute it weather the if is true or false. how can i tell sql i'm not done with the if statement yet?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 00:17:51

use a begin . . . end block

if <condition>
begin
    <statements>
end

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-24 : 00:22:52

AWSOME!!! didnt think it worked like that. thanks!

another thing (i know, i know)...

i'm using the sp_makewebtask procedure to output the data to excel. so how would i:

a) delete the excel file programatically prior to exporting the data or
b) tell the sp_makewebtask procedure to overwrite any pre-existing file

i think that'll be it for the night!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 01:00:33

not familiar with sp_makewebtask but for (a) you can use xp_cmdshell to delete the file

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-10-24 : 01:15:40

PERFECT that does the job. we'll talk more later :o)

Have a great night/day/whatever it is in singapore :o)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 01:46:15