Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.



Thanks for your registration, follow us on our social networks to keep up-to-date