[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql server complex query, then email IF

Posted on 2014-01-21
10
Medium Priority
?
379 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 70

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 70

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 70

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 70

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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