I have a proc which checks trade data for suspect/invalid data; like <0 prices, invalid symbols, <0 quantities...things of this nature.
now, if any suspect data is found, the proc returns it like this:
>>>>>>>
Suspect Trades - UserIDs:
6 identifying fields of the trades found with suspect userids....
Suspect Trades - Symbols:
6 identifying fields of the trades found w/suspect symbols....
Suspect Trades - Quantity:
6 identifying fields of the trades found w/suspect quantities...
>>>>>>>>>>>>
See...if suspect data is found, it is returned for the user to review. If not, nothing is returnred. There are 7 items checked at each invocation. If any of the seven are found suspect, an email is sent.
All this is fine right now, i created the proc, then created suspect data, they're detected and the notice is sent just fine. This is done on a scheduled basis, every 5 minutes, everything works just fine. BUT, I just now deployed it to the prod box, ran it, and found it to come back clean, as I had forgotten to make some suspect data, so everything was good, and i got a blank email
If nothing is found suspect, I'd really like to refrain from sending an email. So, I thought I'd do it like this:
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
but, apparently i can't do that with the EXEC, or my syntax is just bad.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
i really don't want to send blank emails...these are alerts....they should only be sent if/when a value is found to be suspect
can anybody provide some insight? please
CREATE proc dbo.procedurename @output bit output
AS
SET NOCOUNT ON
SET @output = 0
--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
SET @output = 1
PRINT 'Suspect Fills - USERID/acctids:'
SELECT USERID,AcctID,EndPoint,Sym
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
SET @output = 1
PRINT 'Suspect Fills - Symbols:'
SELECT USERID,acctid,EndPoint,Sym
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
SET @output = 1
PRINT 'Suspect fills - side:'
SELECT USERID,acctid,EndPoint,Sym
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.db
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
SET @output = 1
PRINT 'Suspect fills - EndPoint:'
SELECT USERID,acctid,EndPoint,Sym
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
SET @output = 1
PRINT 'Suspect fills - Price:'
SELECT USERID,acctid,EndPoint,Sym
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
SET @output = 1
PRINT 'Suspect fills - Quantity:'
SELECT USERID,acctid,EndPoint,Sym
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
SET @output = 1
PRINT 'Suspect fills - Time:'
SELECT USERID,acctid,EndPoint,Sym
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
return @output
GO