Say I have three tables or four tables that I want to check for values present and I don't want any of them to be null. IF they are null, I will send an email notification per row. What would be the best way to go about this? Should I make a stored procedure with IF statements separately?
I say this because all these tables are not having any constraints, they just pull data out of an excel sheet. So making a table type variable and unioning or inner joining returns nothing as even the column names are different, but the information they store is the same kind of information. So for example if in one table, a column exists called 'DueDate', in the other table, this same sort of information is called 'PaymentDate'. How would I perform such a check on all these tables?
Update: Here's what I've thought of as of now: Say the two tables are A and B, then
DECLARE @messageBody NVARCHAR(MAX) DECLARE @iterator int DECLARE @Rowcount int DECLARE @varTableA TABLE(tablecounter int IDENTITY(1,1), DueDate date, Account NVARCHAR(20), Retailer NVARCHAR(20), Interest float) INSERT INTO @varTableA (DueDate, Account, Retailer, Interest) SELECT DueDate, Account, Retailer, Interest FROM TableA WHERE DueDate IS NULL OR Account IS NULL OR Retailer IS NULL OR Interest IS NULL SET @Rowcount = @@ROWCOUNT SET @iterator = 1 WHILE(@iterator <= @Rowcount) BEGIN SET @messageBody = (SELECT 'No values in following columns' + 'Duedate: ' + ISNULL(Duedate, 'No value') + CHAR(10) + CHAR(13) + 'Account: ' + ISNULL(Account, 'No value') + CHAR(10) + CHAR(13) + 'Retailer: ' + ISNULL(Retailer, 'No Value') + CHAR(10) + CHAR(13) + 'Interest: ' + ISNULL(Interest, 'No Value') + CHAR(10) + CHAR(13) FROM @varTableA WHERE @tablecounter = @iterator) EXEC msdb.dbo.sp_send_dbmail @profilename = [name], @recipients = [reclist], @subject = [sub], @body = @messageBody SET @iterator = @iterator + 1 END
And to repeat this in either the same or a separate stored Procedure for TableB and Table C. Is there a better way of doing this or do you think I am going about this the right way?