Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Bring Array Mapping Capabilities to SQL : Page 4

Simplify your SQL tasks by giving your queries and stored procedures the ability to iterate over arrays of table names or values.


advertisement
Example: A Permuted Mapping
For the next example, create a simple table called Permissions. The DDL for the table is:

CREATE TABLE [dbo].[Permissions]( [IDPermission] [int] IDENTITY(1,1) NOT NULL, [IDUser] [int] NOT NULL, [IDPermissionType] [int] NOT NULL, [PermGranted] [bit] NOT NULL )

Consider this scenario: The Permissions table you just created contains permissions for each user in your system. Each row in the table specifies a user ID, a permission type, and whether the permission is granted or not. You have a list of six new user IDs, and you want to grant each the same set of eight permissions. To do that, you'd have to insert 48 rows in the Permissions table, one row for each user for each permission. But you can accomplish this with a single call to SP_map. The solution below shows five lines of code only because it uses variables for clarity; however, if you put the lists directly in the procedure call, it really requires only one line of code. This example is two-dimensional, requiring two placeholders. Contrast that with the previous example, which used but a single list and a single placeholder in the template.

DECLARE @USERS VARCHAR(50) SET @USERS = '913,1425,11223,739,1234,1001' DECLARE @PERMS VARCHAR(50) SET @PERMS = '2,3,5,7,11,13,17,19' EXEC SP_map @StmtTemplate='INSERT INTO Permissions VALUES(<MAIN_ARG>, <AUX_ARG>, 1)', @MainArray=@USERS, @AuxArray=@PERMS, @Permute=1, @Verbose=1, @Process=0

With @Verbose set to 1, SP_map emits this generated code:



-- INSERT INTO Permissions VALUES(913, <AUX_ARG>, 1) -- INSERT INTO Permissions VALUES(1425, <AUX_ARG>, 1) -- INSERT INTO Permissions VALUES(11223, <AUX_ARG>, 1) -- INSERT INTO Permissions VALUES(739, <AUX_ARG>, 1) -- INSERT INTO Permissions VALUES(1234, <AUX_ARG>, 1) -- INSERT INTO Permissions VALUES(1001, <AUX_ARG>, 1)

Note that each line begins with the SQL comment marker. Why? Because these are not executable SQL statements! SP_map has replaced the <MAIN_ARG> placeholder in the @StmtTemplate with values from the @USERS list, but not the <AUX_ARG> placeholder. SP_map operates on two-dimensional combinations in the obvious way: For each user, it does something (which creates the statements above), and then it does something for each permission. In other words, this is just two nested loops. Therefore, setting @Verbose to 1 shows details of the outer loop, while setting @Verbose to 2 also shows details of the inner loop. The end product of the inner loop is a set of finished DML statements, so these are not prefaced with the comment marker:

-- INSERT INTO Permissions VALUES(913, <AUX_ARG>, 1) INSERT INTO Permissions VALUES(913, 2, 1) INSERT INTO Permissions VALUES(913, 3, 1) INSERT INTO Permissions VALUES(913, 5, 1) INSERT INTO Permissions VALUES(913, 7, 1) INSERT INTO Permissions VALUES(913, 11, 1) INSERT INTO Permissions VALUES(913, 13, 1) INSERT INTO Permissions VALUES(913, 17, 1) INSERT INTO Permissions VALUES(913, 19, 1) -- INSERT INTO Permissions VALUES(1425, <AUX_ARG>, 1) INSERT INTO Permissions VALUES(1425, 2, 1) INSERT INTO Permissions VALUES(1425, 3, 1) INSERT INTO Permissions VALUES(1425, 5, 1) INSERT INTO Permissions VALUES(1425, 7, 1) INSERT INTO Permissions VALUES(1425, 11, 1) INSERT INTO Permissions VALUES(1425, 13, 1) INSERT INTO Permissions VALUES(1425, 17, 1) INSERT INTO Permissions VALUES(1425, 19, 1) -- INSERT INTO Permissions VALUES(11223, <AUX_ARG>, 1) INSERT INTO Permissions VALUES(11223, 2, 1) INSERT INTO Permissions VALUES(11223, 3, 1) INSERT INTO Permissions VALUES(11223, 5, 1) INSERT INTO Permissions VALUES(11223, 7, 1) INSERT INTO Permissions VALUES(11223, 11, 1) . . .

Figure 5 shows the contents of the Permissions table after the preceding code has executed.

 
Figure 5. Results of a Permuted Mapping: All combinations of users and permissions are inserted into the Permissions table.

 
Figure 6. Results of a Non-Permuted Mapping: The cardinality of the two input arrays must agree, as they are consumed in pairs in a lock-step fashion.
This example so far has dealt with a two-dimensional scenario with the @Permute flag turned on. What happens if you turn it off?

Author's Note: Empty the Permissions table to start fresh before executing the code shown in Figure 6.

When @Permute is off, the two lists are constrained to be of the same cardinality, so you would need to modify your user and permissions list to have the same number of elements, as in Figure 6.

You can see in Figure 6 that the procedure takes the elements of @MainArray and @AuxArray in a pair-wise fashion, until both are used up. A key observation is that when you run the procedure with @Permute set to 0, order matters! With the permuted example shown previously, the ordering of the elements in the two lists was not significant. (Note that order may matter whether you activate @Permute or not, because database operations almost always produce side effects that can cause changes in a persistent database.)



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap