We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

If count > 0 subject line = this

Medium Priority
372 Views
Last Modified: 2012-05-11
How can I get this to work in sql server 2008 r2. I am used to doing this in VFP...

I want to query a DB
If the the records returned are > 0 the Email subect is xyz

Else the email subject is 123
SELECT field1, field2....;
FROM MyTable;
Where x=50;
ORDER BY y INTO Cursor MyCursor
 
COUNT to m.count
IF m.count > 0 
DO email.prg "Here is the data"..........

ELSE
DO email.prg "*** NO DATA ***"........

Open in new window

Comment
Watch Question

If I understand correctly, couldn't use just the SQL case statement to evaluate whether your count is 0, return 1 if so, else return ABC?

CASE x when 0 then 1 else "ABC"



ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Not sure if I fully understood your Q, but maybe something like the following?

The code is using a table variable.  There are two places where you need to replace the <blabla> with your customized field list.
declare @t table
(
	<define your fields here>
);
declare @count int;

insert into @t
SELECT <your fields>
FROM [S862].[RejectAudit];

select @count = COUNT(*) from @t;
if @count = 0
	select 123;
else
	select * from @t;

Open in new window

BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:
hi,

check out this...


if exists(
SELECT field1
FROM MyTable;
Where x=50;
)
begin
print'Here is the data..........'
end
ELSE
begin
print '*** NO DATA ***........'
end

Author

Commented:
I just want a way to pass the 'subeject line' as 'here is the data if there is data from the select
or if there was no data from the select the 'subjetc line' would be *** no data today ***


the subject line is the report services subscription.

SharathData Engineer
CERTIFIED EXPERT

Commented:
Is email.prg an SP or external function? Instead of printing the statement, you can invoke email.prg with proper subject line based on row count.
if exists(
SELECT field1
FROM MyTable;
Where x=50;
)
begin
email.prg "Here is the data"..........
end
ELSE
begin
email.prg '*** NO DATA ***........'
end

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.