Solved

sql server complex query, then email IF

Posted on 2014-01-21
10
370 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query stumper 3 37
Need to update TableA to TableB 6 34
Help Parsing a String with SQL Syntax 23 28
How do I Start SQL Management Studio 2012 17 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

17 Experts available now in Live!

Get 1:1 Help Now