IF EXISTS (EXEC..... )

what if i first create a temp table, insert into that table the results from the proc, and if exists on that table.  if results are there, i update my flag for the mail.  what do you think about that?  is there an easier/better way to do this?

IF EXISTS (exec proc....)
 the above is not permissible, you can do either of these
1. CREATE a temp table with the exact structure the sp returns and insert the o/p of the sp in this
    INSERT INTO #temp
    EXEC proc
 
    IF EXISTS (SELECT 1FROM #temp )

2. change the sp to a table valued function  and use it
  IF EXISTS (SELECT 1 FROM dbo.functionName)

hi aneesh.  looks like we were both on the same page, same time (item 1)
i'm going to try that.  can you explain #2 to me a bit?

you can change your procedure to a function wich returns a table, so insteatd of the 1st option, you can do this

CREATE FUNCTION fn_functionname()
RETURNS @t table (col1 int, col2 int ... )
AS
BEGIN
        --- code
END

i'm sorry, aneesh, i'm not sure what you mean.  i've got a great big proc...how do i change this to a function, and then call it like option 1 ?

and, the proc takes about 13 seconds to return, this if exists into a tmp table is still running at 56 sec??

dbaSQL,


SET ROWCOUNT 1
insert into #Temp
EXEC sps

no, still the same t hing, aneesh.  the proc is quite fast.  this thing is still running.  my syntax, maybe?  here's the whole piece - that part at the top is just something i run to ensure it is only ran on a business day -

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

create table #a (bunch of fields...)

SET ROWCOUNT 1
insert into #a exec dba_stat.dbo.usp_procname

if exists (select * from #a)
begin
      update dba_stat.dbo.table SET sentflag = 0 WHERE subject = 'this particular subject'
end
drop table #a
END

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

create table #a (bunch of fields...)

SET ROWCOUNT 1
insert into #a exec dba_stat.dbo.usp_procname
SET ROWCOUNT 0 --- U need to insert this line too
if exists (select * from #a)
begin
     update dba_stat.dbo.table SET sentflag = 0 WHERE subject = 'this particular subject'
end
drop table #a
END

it's still terrible, aneesh.  i can't figure out what's doing this.  it ran 4 minutes, i cancelled, it did that for another 4 minutes.
again, the proc is nearly immediate compared to this
possibly the function?  but i'm not sure how to do that the best way

and there are a few tmp tables in my proc

attempt to create the funciton fails with msg 2772
cannot access temporary tables from within a function

ok, dismiss the function, aneesh, can you help me w/the temp table proc?

well, aneesh, i thought i got it w/this:

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
      update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END

it came back faster - about 30 seconds - but it's not updating that flag to 0

do you have any ideas?

rafrancisco put this out there once:  

>>>>>You can put a condition before sending the email to check if there are records in the table:

IF EXISTS (SELECT * FROM db1.dbo.OP_STATUS6_STUCK)
    EXEC master..xp_sendmail @recipients = 'me',
            @query = 'SELECT * FROM db1.dbo.OP_STATUS6_STUCK ;',
            @subject = 'Customer Service - Message1',
            @message = 'Attached file is a list of Problem Orders',
            @attach_results = 'TRUE',
            @width = 500

how can i do the same thing?  this is exactly what i'm trying to do -- only send the email if there are results
or, specifically, only perform that update if the procedure has results

>>IF EXISTS (exec proc....)
>>begin
>>   update a flag value which ensures the email is sent IF it is updated to a certain value
>>end
>>end

many do this update inside the proc. why checking the proc and then updating.
cutsomize the procedure to include this update statement.

imran, thank you, but i'm just not sure i follow you.
and i'm just trying to check to see if the proc has results, if it does, then update the flag.  you know what i mean?  i'd be doing this inside an agent job.  if that flag is updated, a mail is sent.   if not, the job completes and no mail is sent, as is desired

hi dbaSql,
Should have any output parameter inside the procedure and do someting like this

like

Create YourProc @input datatype, @output bit output
begin
--your code
if count(*) >1 then set @output = 1
return
end


Declare @output datatype
Exec Yourproc 'inputvalue',@output output
IF @output =1
begin
  update a flag value which ensures the email is sent IF it is updated to a certain value
end
>>end

imran, again, thank you.  but could you walk me thru this a bit more?  
my procedure has no input, it's simply statistical analysis.  so,

create my proc @output bit output
..bunch of code
if count(*) >1 then set @output = 1
return
end

run my proc, am i only getting the output, what of the results, if/when there are any?

the second chunk, i believe i get.  this will be simply the driver that updates the flag, or not
i'm going to try the first piece, but i'm unsure

>>run my proc, am i only getting the output, what of the results, if/when there are any?

post your procedure my be i can change it but it will return only output which you have to set something like the below.

create my proc @output bit output
you code what ever you are doing then you have to see if there are records
if count(*) >1 then
set @output = 1
return
end

oh, imran, it's a big one.  i've genericized, where necessary.  please do let me know what you think.  i am up against the wall on this one now, badly, i really do appreciate your input.

as you can see herein, there are a handful of attributes we are concerened about realtime.  if/when anything is found to be suspect, an alert must be sent.  i plan to schedule this...as soon as i can avoid sending an email if/when there is no suspect data.  ( if you see anything sreamingly-bad, please point it out )

CREATE proc dbo.procedurename
AS

SET NOCOUNT ON

--USERID/ACCTID CHECKER
create table #USERIDs (USERID varchar(20),acctid varchar(20))
insert #USERIDs select distinct USERID,acctid
from database.dbo.USERIDs
where active = 'y'

IF EXISTS (
SELECT DISTINCT t.USERID,t.acctid
FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
AND acctid = t.acctid
) )
BEGIN  
      PRINT 'Suspect Fills   - USERID/acctids:'
      SELECT USERID,AcctID,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
      AND acctid = t.acctid )
      OPTION (MAXDOP 1)
END

--SYMBOL CHECKER
create table #symbolsA (symbol varchar(8))
insert #symbolsA select '?'
union
select ''
union
select 'UNKNOWN'

IF EXISTS (
SELECT DISTINCT symbol
FROM database.dbo.table T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND EXISTS (
SELECT 1 FROM #symbolsA
WHERE symbol = t.symbol)
)
BEGIN
      PRINT 'Suspect Fills   - Symbols:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND EXISTS (
      SELECT 1 FROM #symbolsA WHERE symbol = t.symbol)
      OPTION (MAXDOP 1)
END

--SIDE CHECKER
create table #side (side char(1))
insert #side select 'B'
union
select 'S'

IF EXISTS (
SELECT DISTINCT [B/S]
FROM database.dbo.table T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #side
WHERE side = t.side)
)
BEGIN
      PRINT 'Suspect fills   - side:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #side WHERE side = t.side)
      OPTION (MAXDOP 1)
END

--ENDPOINT CHECKER
create table #endpoints (endpoint varchar(8))
insert into #endpoints select distinct endpoint from servername.databasename.dbo.tablename

IF EXISTS (
SELECT DISTINCT t.EndPoint
FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint) )
BEGIN
      PRINT 'Suspect fills   - EndPoint:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint )
      OPTION (MAXDOP 1)
END

--PRICE CHECKER
create table #symbolsB (symbol varchar(16))
insert into #symbolsB select distinct symbol from database.dbo.table where symbol like  '%[-:]%'

IF EXISTS ( SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND price <=0
AND NOT EXISTS (
SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
)
BEGIN
      PRINT 'Suspect fills   - Price:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND price <=0
      AND NOT EXISTS (
      SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
      OPTION (MAXDOP 1)
END

--QUANTITY CHECKER
IF EXISTS ( SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND quantity <=0
)
BEGIN
      PRINT 'Suspect fills   - Quantity:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND quantity <=0
      OPTION (MAXDOP 1)
END

--TIME CHECKER
IF EXISTS (
SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime >= DATEADD(mi,61,getdate() ))
BEGIN
      PRINT 'Suspect fills   - Time:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime >= DATEADD(mi,61,getdate())
      OPTION (MAXDOP 1)
END

DROP TABLE #USERIDs
DROP TABLE #symbolsA
DROP TABLE #side
DROP TABLE #endpoints
DROP TABLE #symbolsB

SET NOCOUNT OFF
GO

Server: Msg 147, Level 15, State 1, Procedure usp_SanityChk, Line 186
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


tring to revise the proc

ok, got it, imran.  and it runs.  i need to create some invalids right now so i can test.
but can you walk me thru this - what's the right way to only update the flag if i've got output?

>>>>
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END
>>>>>>>>>>>

it does exactly what my other proc did, only the output param is required.  so, exec procname 1 gives me the data.
what's the right way to call it herein:


SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END

no, same as before, it seems to run, except the data is returned and the value is not updated.

Server: Msg 7391, Level 16, State 1, Procedure procname, Line 15
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


create table #a (...bunch of fields....)

insert into #a exec database.dbo.procname 1
if exists (select * from #a)
begin
     update database.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
end
drop table #a
END

drop table #a

call it like this

declare @spcount int
exec  dbo.procname, @spcount output

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END

sorry no need for the comma


call it like this

declare @spcount int
exec  dbo.procname @spcount output

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END

as i said, it runs, the results are returned, the value is not updated
i really need to get this into place.  i just cannot see why the update isn't occurring

print @spcount and see what is the value you are getting for it.

declare @spcount bit

exec  dbo.procname @spcount output
print @spcount
if @spcount = 1
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END

it has to be the way i'm calling it.  there is suspect data, the proc returns the results, but the value isn't updated.  so an email won't be sent.  do you see it?

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec db.dbo.procname @spcount output

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END

where you are sending mail i don't see it  is see this update statement

>> update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'

but did you check
print @spcount what is it printing in the messege tab 0 or some value.

ok, i don't know how to voice this
-it is 2 before i run it
-it prints as 0
-i select again after it completes, it's 2
-the update is not run

select sentflag,subject from table where subject = 'this subject'

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount bit
exec db.dbo.procname @spcount output
print @spcount

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END

i'm not sending it in the text i've placed thus far.  the statement i'm trying is step 1 of an agent job.  step two sends the email, if in fact the value has been updated to 0.  anything other than 0 will not be sent

if it prints as 0, there's my problem.  that's why there is no update.  what the heck am i missing here?

Man look at the print @spcount it is printing 0 if it prints 0 then it means the stroed procedure return 0 so if you check
below condition will fail and hence wont update

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END


Did you change your db.dbo.procname as i suggested because currently if you see this @spcount 0 so check your stored procedure db.dbo.procname and modify it so that it will update the value of @spcount

yes, i believe i just found my error.  give me a second

oh good lord.  my bad, imran.  i am very sorry for dragging you through that oversight with me
i'm going to complete the test now....gen an email...i'll let you know shortly

yep.  it works.
my mistake was that i only put the beginning and ending set @ouptu in there.  which, of course, was setting the thing to 0
i didn't have the individual ones in there at each validation check

thank you, imran.  big time

imran, can i do the same thing with a procedure which also has parameters?  
i'm trying, but thus far it's not taking it syntactically

>>imran, can i do the same thing with a procedure which also has parameters?  

Yes

something like

Create Procecure procedurename @inputpar int, @outppar int

ok, yes, i tried/am trying that, imran.  a couple problems, but i think i'm getting there.  thank you much