Sql-server – Launch a stored procedure when a datetime is expired

sql serversql-server-2016stored-procedures

Can I launch in SQL Server a stored procedure when a datetime value (stored in a table) is expired?

I have a stored procedure that will send a message with MSMQ, and I need to do this as soon as the datetime is expired.

I am using SQL Server 2016.

Best Answer

Here is another option:

It would require a little more 'setup', but have a look at Service Broker timer and activation. Here is an example of how you might utilize this functionality. Service Broker even works on Sql Express (which doesn't have the Sql Agent).

Related Solutions

Mysql – Stored Procedure – MySQL

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.

Sql-server – stored procedure update retain values

If I understood correctly, if stored procedure is called with @vCol1 = 'AA', @vCol2 = 'BB' and @vCol3 = NULL then you want to update only Col1 and Col2 keeping the Col3 value intact. Then you can go with the following solution. I agree its not the best one but it will get work done in simple manner.

UPDATE table
SET col1 = ISNULL(@vCol1, col1)
  , col2 = ISNULL(@vCol2, col2)
  , col3 = ISNULL(@vCol3, col3)
WHERE id = 1

Best Answer

Related Solutions

Mysql – Stored Procedure – MySQL

Sql-server – stored procedure update retain values

Related Question