Use arrays and Sets in Stored Procedure

By Perry

INSTANTLY dtSearch TERABYTES OF POPULAR DATA TYPES; hundreds of reviews, etc.!

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.

Popularity (674 Views)