@@ROWCOUNT values in SQL

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Dear Friends,

I have created a Procedure and i have used if exists to get the values.

SQL

SELECT * FROM MY_DATA WITH (NOLOCK) 
    IF @@ROWCOUNT>=1
        BEGIN
            SELECT * FROM MY_DETAIL WITH (NOLOCK)
        END
    ELSE
        BEGIN
            SELECT 'NO RECORDS'
        END

In this I have getting My_Data values plus My_detail table values.
But I want to get only My_Detail table value, how to avoid My_data records.
Kindly suggest for the same.

Posted 20-Aug-12 1:50am

Arunprasath Natarajan

Add a Solution

2 solutions

Solution 2

SQL

SELECT * FROM MY_DETAIL WITH (NOLOCK)
WHERE EXISTS (SELECT MAX(1) FROM MY_DATA WITH (NOLOCK) )

Posted 20-Aug-12 17:22pm

Kuthuparakkal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-20T01:53:00

Solution 1

SQL

declare @count varchar(50)
select @count=count(1) from  MY_DATA WITH (NOLOCK) 
IF @count>=1
       BEGIN
           SELECT * FROM MY_DETAIL WITH (NOLOCK)
       END
   ELSE
       BEGIN
           SELECT 'NO RECORDS'
       END

Posted 20-Aug-12 1:53am

Santhosh Kumar Jayaraman

Comments

Arunprasath Natarajan 20-Aug-12 7:55am

Do I want to avoid @@Rowcount here?

Santhosh Kumar Jayaraman 20-Aug-12 7:58am

If you use this,
SELECT * FROM MY_DATA WITH (NOLOCK)

you will get output of this.So instead of @@rowcount, i am gettin count .so only FROM select * from MY_DETAIL WITH (NOLOCK) will display records

Arunprasath Natarajan 20-Aug-12 8:03am

Yeah I understand that, But what i am trying to ask is, At this conditions @@Rowcount is advisable or not. If yes, then is there any option to get the last query data alone.

Santhosh Kumar Jayaraman 20-Aug-12 8:06am

No its not required..@count is enough

Arunprasath Natarajan 20-Aug-12 8:08am

Tan q. will follow the same.

Santhosh Kumar Jayaraman 20-Aug-12 8:08am

Usually they use rowcount to know how many records inserted, updated and deleted. They dont use it with select queries. Count(1) will be faster than select * and @@rowcount. SO i would say dont use @@rowcount

Arunprasath Natarajan 20-Aug-12 8:40am

Tan q fr ur explanation. Now I am clear. If any more difficulties then I will get back to you.

Arunprasath Natarajan 20-Aug-12 8:56am

I have doubt, how this count(1) works faster then count(*), is it any thing like index based.

Santhosh Kumar Jayaraman 20-Aug-12 8:59am

Yes.But it depends
Check this.
http://www.bradleyschacht.com/select-count-vs-count1-vs-countcolumnname/