Solved

sql server complex query, then email IF

Posted on 2014-01-21
10
374 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

742 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