Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Store Count as a variable

Posted on 2011-05-12
11
Medium Priority
?
298 Views
Last Modified: 2012-05-11
I want to be able to run my SELECT
have an indicator if there are zero or one or more records

so that way if records <1 nothing happens
ELSE
DO ....

0
Comment
Question by:jaymz69
  • 6
  • 4
11 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35750338
Hi.

You can look at @@ROWCOUNT http://technet.microsoft.com/en-us/library/ms187316.aspx and see if it is helpful to you in your script.  
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35750541
One simple way:

If exists (select somecolumn from sometable) begin
--1+ records
End
Else begin
--0 records
End
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35750620
I agree with Patrick.  If you don't really need to store the row count, then I would just use EXISTS as shown.  If you need to do a selection, then do additional work based on the number of rows that is where @@ROWCOUNT would come in handy.

EXISTS method is much more efficient, especially if you don't actually care what the count is -- just that it is more than 0.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:jaymz69
ID: 35750714
Even in an a OPENQUERY?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35750729
Please explain, what is it you are trying to do exactly.  A post of the current query would be helpful also and point out where you need the conditional based on number of rows.  
0
 

Author Comment

by:jaymz69
ID: 35750736
If there any records then I want it to Email the subscribed users

Else do nothing until the next scheduled time to run
SELECT iruser 'User',
	COUNT(*) '# of RA Lines'

FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE loc = 1
	AND irsrc = ''R''
	AND irdate = (CURRENT_DATE - 1 DAYS)	
')
GROUP BY iruser

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35750750
If you are trying to do an update or something, then you would just add the EXISTS in WHERE condition by the way.

i.e.,
UPDATE your_table
...
WHERE EXISTS (SELECT 1 FROM some_other_table WHERE some_other_table.column = your_table.column)

I have also done something like this with UNION if you are needing to do a different selection based on non-existence of a primary query:

SELECT ...
FROM your_table_1
UNION
SELECT ...
FROM your_table_2
WHERE NOT EXISTS (SELECT 1 FROM your_table_1)

The second bit only fires if the first doesn't yield results ... caveats of column count, data type, etc. as with any UNION query but under right circumstances can be very effective.

If you have some other purpose in OPENQUERY, please expand on what it is you are looking for.

Thanks!
0
 

Author Comment

by:jaymz69
ID: 35750767
I basically just have a SSRS scheduled to run and if there is no records I do not want it to Email the users with a blank Email.

0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35750768
Didn't see your last post, sorry.

If I understand correctly, that is what I thought previously:

-- your select
SELECT iruser 'User',
      COUNT(*) '# of RA Lines'

FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE loc = 1
      AND irsrc = ''R''
      AND irdate = (CURRENT_DATE - 1 DAYS)      
')
GROUP BY iruser

-- notify based on @@ROWCOUNT
IF (@@ROWCOUNT > 0)
BEGIN
   -- send e-mail here
END
0
 

Author Comment

by:jaymz69
ID: 35750799
So @@ROWCOUNT is a system variable that automatically stores the ROW Count?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35751574
Yes, you got it.  See the technet article for more details like when to use ROWCOUNT_BIG.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question