Use arrays and Sets in Stored Procedure

Example to use use arrays and Sets in Stored Procedure.

You can use SetToArray, StrToSet, and SetToStr functions for this example.

The following example returns a set containing all the members of the Time dimension:

StrToSet("Time.Members")

The query below returns a string for the supplied set. 

WITH 
      MEMBER Measures].[SetString] AS
      SETTOSTR([Sales Reason].[SalesReasons].[Sales Reason].[Review]) 
SELECT 
      {[Measures].[SetString]} 
ON ROWS 
FROM
      [Adventure Works]


SELECT StrToSet ('[Geography].[State-Province].Members')
ON 0 FROM [Adventure Works]

Refer http://msdn.microsoft.com/en-us/library/ms144782.aspx for details.

By Perry Popularity (948 Views)