Bring Array Mapping Capabilities to SQL : Page 5

Example: Mapping With Dynamic Inputs
For this next example, consider if, instead of passing a hard-coded list of user IDs, you select a subset of users from a Users table. To follow along, create a basic Users table and populate it with this code:

   CREATE TABLE [dbo].[Users](
       [IDUser] int IDENTITY(1,1) NOT NULL,
       [LastName] varchar(50) NULL,
       [FirstName] varchar(50) NULL
   )
   INSERT INTO dbo.Users VALUES ('Smith', 'A')
   INSERT INTO dbo.Users VALUES ('Jones', 'C')
   INSERT INTO dbo.Users VALUES ('Smith', 'B')
   INSERT INTO dbo.Users VALUES ('Smith', 'D')
   INSERT INTO dbo.Users VALUES ('Smithson', 'W')
   INSERT INTO dbo.Users VALUES ('Miller', 'R')
   INSERT INTO dbo.Users VALUES ('Smith', 'Q')
   INSERT INTO dbo.Users VALUES ('Rice', 'O')
   

This code again uses the Permissions table created earlier, but draws users from the Users table rather than a constant list:

   DECLARE @PERMS VARCHAR(50)
   SET @PERMS = '2,3,5'
   
   EXEC SP_map
       'INSERT INTO Permissions VALUES(
          <MAIN_ARG>, <AUX_ARG>, 1)',
       @MainArray='SELECT IDUser from dbo.Users 

 
Figure 7. Dynamic Input Array: Instead of a constant list, you may use a query for either or both of the input arrays.

          WHERE LastName LIKE ''Smith%''',
       @AuxArray=@PERMS, @Permute=1, 
       @Verbose=1

Note that the user list is still passed in through the @MainArray parameter, but instead of a constant list, it is a SELECT statement. Because the @Permute flag is on, SP_map instantiates all combinations of users like 'Smith%' (there are five) with the three-element @AuxArray list. Figure 7 shows the results.

When using dynamic inputs, keep these points in mind:

• You may use a dynamic list in place of a constant list for either @MainArray or @AuxArray.
• You may use either a SELECT statement or an EXECUTE statement to formulate your query.
• The query must return a result set containing only a single column of type VARCHAR(128).

Previous Page

Next Page