• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

If count > 0 subject line = this

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

0
jaymz69
Asked:
jaymz69
1 Solution
 
AdamRobinsonCommented:
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"



0
 
ValentinoVBI ConsultantCommented:
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

0
 
ValentinoVBI ConsultantCommented:
once again I clicked submit too fast: here's the updated code...  three places with <blabla> now...
declare @t table
(
        <define your fields here>
);
declare @count int;

insert into @t
SELECT <your fields>
FROM <your table>;

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

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Bhavesh ShahLead AnalysistCommented:
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

0
 
jaymz69Author 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.

0
 
SharathData EngineerCommented:
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

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now