There are times when you need to apply a routine to all rows in a table. T-SQL provides a handful of declarative routines for transforming, filtering and aggregating data; and syntactic sugar like CROSS APPLY allows you to interweave function application into queries. However because T-SQL functions are stateless, you can’t use them to update, insert or delete data directly or call external procedures. I was surprised that there is no standard syntax for applying stored procedures in the same manner that CROSS APPLY does for functions. sp_Map and sp_ForEach provide a mechanism to invoke stored procedures on each row in a small table.
/* sp_MAP * T-SQL implementation of the `map` function borrowed from the functional paradigm * * Requires a staging table, `myQueue`. This means sp_Map is not a general or dynamic * function, it requires some thought out system design to consider where this would * be most useful to use within your system. * * Parameters: * @x : a decrementor variable for the recursive case * @sproc : the function we want to apply to myQueue * */ CREATE PROCEDURE sp_Map (@x int, @sproc sysname) AS IF @x > 0 BEGIN -- you cannot pass expressions into a sproc, hence the variables DECLARE @chi int = @x - 1; DECLARE @msg varchar(128) = (SELECT msg FROM myQueue WHERE msgId = @x); -- invoke @sproc on myQueue EXECUTE @sproc @msg; -- recursive call EXECUTE dbo.sp_Map @chi, @sproc; END
sp_Map is a conceptual prototype only. It takes concepts from the functional paradigm, like higher-order programming and data abstraction, to apply a certain sproc, @sproc, to some generic queue. The application of the sproc upon the queue is not dynamic, I deliberately wanted to avoid using dynamic SQL. Because of this limitation, you would have to design the call to @sproc and the staging table, myQueue, so that they were compatible. A simple example that prints out a message is given below:
USE tempdb; go -- Create a Message Queue CREATE TABLE myQueue (msgId int, msg varchar(128)); go INSERT INTO myQueue SELECT 1, 'having coffee' UNION SELECT 2, 'arriving at the cafe' UNION SELECT 3, 'walking into town' go /* sp_Print * A Simple test sproc that prints a message */ CREATE PROCEDURE sp_Print (@msg varchar(128)) AS PRINT @msg; go -- Map sp_Print onto myQueue EXECUTE sp_Map @sproc='myQueue', @queue='myQueue'; -- OUTPUT --> "walking into town" --> "arriving at the cafe" --> "having Coffee"
Limitations of sp_Map
Recursion and SQL Server:
- SQL Server has a maximum recursion limit of 32. This prevents performance issues resulting from uncontrolled growth of the call stack
- Perhaps this is not a limitation, but a practical restriction on the use of sp_Map. The recursion limit means that you would have to take sensible precautions as to when sp_Map was to be used, and limit it to small queues only
- sp_Map is quite specific. I haven’t been able to figure out how to generalise it so that it can successfully map a variety of sprocs (e.g. you would need a sp_Map for sprocs that take a single argument, another for sprocs that take more than one argument etc.)
- As before, this may not be a giant issue, as it is tailor-made code, that you are likely to design to fit your environment
Finally, below is an imperative application of sp_Map, named sp_ForEach. sp_ForEach solves the limited recursion limit imposed by SQL Server. I don’t like this as much; there is nothing stopping you from calling sp_ForEach with a queue length in the thousands and this would be a horrible performance issue. But for completeness, it is below:
CREATE PROCEDURE sp_ForEach (@sproc sysname) AS DECLARE @queueLength varchar(128) = (SELECT COUNT(*) FROM myQueue); WHILE @queueLength > 0 BEGIN DECLARE @msg varchar(128) = (SELECT msg FROM myQueue WHERE msgID = @queueLength); EXECUTE @sproc @msg; SET @queueLength = @queueLength - 1; END