about system variable

i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

about store proc

Hi i have store proc where i am excuting a query look like
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:

> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:

> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>