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 5

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


advertisement
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).


Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap