database / SQL Server DBA

sp_Map and sp_ForEach

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

Limited application:

  • 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s