?
Solved

sql server complex query, then email IF

Posted on 2014-01-21
10
Medium Priority
?
376 Views
Last Modified: 2014-01-23
I am trying to run a sql query something like this:

I need to run this first statement in a loop, pulling  the where divisionInt = should be all the id's in  table 'bkgDivision'

then I need to know how many rows were returned. If more than 1 row is returned, I will execute a sendmail.

How would I write it up?

while ...

use admire
 select * from bkgReciepts where divisionInt = bkgDivision.id ...

   if more than 1 row  then
 ' i know what to put here
end if

next
0
Comment
Question by:rivkamak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39798341
you can write it as below

IF EXISTS( select * from bkgReciepts where divisionInt = bkgDivision.id ...)
BEGIN
 ' i know what to put here
END

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
ID: 39798394
The while that is a cursor  
An example   http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
The syntax http://technet.microsoft.com/en-us/library/ms181441(v=sql.90).aspx

If you are smart your select in the declare cursor has already a filter on the more than 1 rows returned  
group by divisionInt = bkgDivision.id ...
HAVING COUNT(*) > 1

Open in new window

"IF exists"  stands for >= 1 so not usable here
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39798407
this code should find all the matching ids with one query, then cursor thru them to send the emails:



declare @id int
declare csrIds cursor static for
select bd.id
from bkgDivision bd
cross apply (
    select top (2) br.divisionInt
    from bkgReciepts br
    where
        br.divisionInt = bd.id
) AS br2
group by bd.id
having count(*) > 1

open csrIds

while 1 = 1
begin
    fetch next from csrIds into @id
    if @@FETCH_STATUS <> 0
        break
    --ADD code to send email for this id
end --while

deallocate csrIds
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:rivkamak
ID: 39799097
Sorry, I missed one thing.
@: ScottPletcher it worked perfectly.

Once I got more than 1 record  returned and I am sending an email, I need to reference that record in  the bkgDivision, where id = bd.int, and get the rest of the fields,
bkgDivision.email and bkgDivision.name
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39800237
...
while 1 = 1
begin
    fetch next from csrIds into @id
    if @@FETCH_STATUS <> 0
        break
    select @email = bd.email, @name = bd.name --, ...
    from  bkgDivision bd
    where
        bd.id = @id

    --EXEC msdb.dbo.sp_send_dbmail ...
end --while
...
0
 

Author Comment

by:rivkamak
ID: 39801202
I am trying to put this as the message:

SET @Sub = 'You have  receipts that were not looked at. Please go to http://www.oorah.org/receipts/listbymanager.asp?id=' + CAST( @id AS varchar(20)) + ' and look at them.';


but in my email, I only get up to the +, I don't get anything after I try to cast.
Did I code something wrong?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39801275
Not from a SQL Server standpoint.

As to whether or not that specific type of embedded link works in your specific email server and service, I don't know.

Try removing the " + ' and look at them.' " and see if the link works then.
0
 

Author Comment

by:rivkamak
ID: 39804179
You are right, even this gets cut of:

SET @Sub = 'You have  receipts that were not looked at.
 Please go to http://www.oorah.org/receipts/listbymanager.asp?id=37 and look at them.';

it stops here:listbymanag
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39804437
That looks like a truncation issue.

What is the variable declaration for @Sub?

Are any other SQL variable(s) truncating a value?
0
 

Author Closing Comment

by:rivkamak
ID: 39804490
Thank you for all your help . It works beautifully now.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 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