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  (795 Views)