Tukukkk

I have a table that shows events and vehicles associated with those events:

id | model | colour     | cargo

--------------------------------------------

1 | vw       | red      | tomatoes

2 | bmw      | red      | golf clubs

I would like to have a table that I can use to manage alerts. There is a column that represents strings to check for, another column that shows which column/table it is applied to and some text that describes the alert:

id | column_name    | check_string     | alert_string

--------------------------------------------

1 | my_table.colour | red              | 'Vehicle is red'

2 | my_table.cargo  | [sport,club]     | 'Vehicle is carrying sports goods'

or possibly:

id | column_name    | check_string     | alert_string

--------------------------------------------

1 | my_table.colour | red              | 'Vehicle is red'

2 | my_table.cargo  | sport            | 'Vehicle is carrying sports goods'

3 | my_table.cargo  | club             | 'Vehicle is carrying sports goods'

I would like to have a query that I can run that would return all the alerts that apply to that row:

id | alert_text

--------------------------------------------

1 | ['Vehicle is red']

2 | ['Vehicle is red', 'Vehicle is carrying sports goods']

The way I was doing this was building up SQL commands in Python and running them against the database but this becomes a burden as the number of rules grow or variables need to be changed/updated (suddenly we don't care about red cars but are very concerned with blue cars).

SELECT id, 'Vehicle is red' as alert_text FROM my_table 

WHERE my_table.colour = 'red';



SELECT id, 'Sports goods' as alert_text FROM my_table 

WHERE my_table.cargo in ['sport', 'club'];



SELECT <many other rules>;

Is there a better way to do this? Is it worth building a DB table that can dynamically point to a column, strings to check in that column and then alert text associated with the rule? Should I even be using SQL for this problem?

I've got a feeling that SQL is maybe not the right tool for this job but I don't know what I don't know...

This SQL code would allow you to have a DB table of any size for both Events and Alerts

Declare @vSQL nvarchar(Max)

Declare @vColumnName nvarchar(25)

Declare @vCheckString nvarchar(25)

Declare @vAlertString nvarchar(50)



Declare vCursor CURSOR For

  Select [column_name], [check_string], [alert_string] From vehicle_alerts



  Open vCursor;

  Fetch Next From vCursor Into @vColumnName, @vCheckString, @vAlertString



  --Make global temp table from source then delete contents.  

  --Ensures field lengths are as large as the largest value in the temp table (Not the most efficient but it works)

  Select id, alert_string INTO ##alerts From vehicle_alerts

  Delete From ##alerts  



  While @@FETCH_STATUS = 0  --Loop through alerts and interrogate your events using the LIKE operator

  Begin

     Set @vSQL = 'INSERT INTO ##alerts Select id, '''+@vAlertString+''' As [Alert] From vehicle_events Where ' + @vColumnName + ' Like ''%' + @vCheckString + '%'''

     Execute (@vSQL)

     Fetch Next From vCursor Into @vColumnName, @vCheckString, @vAlertString

  End;



  --Use STUFF to combine the alert strings by id

  Select id,STUFF((Select ',' + [alert_string] From ##alerts Where id = a.id FOR XML PATH('')),1,1,'') AS Tmp 

  From ##alerts AS a

  Group By id



  Drop Table ##alerts

  Close vCursor

  Deallocate vCursor