Solved

sql server complex query, then email IF

Posted on 2014-01-21
10
369 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
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:
ScottPletcher earned 500 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
 

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:ScottPletcher
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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:ScottPletcher
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:ScottPletcher
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now