0

How to create a stored procedure in SQL Server which creates dynamic partitions and process?

I have below code which creates a partition every time, and does process at one time in liked server.

Using below code I would like to create dynamic multiple partitions and process by passing partition name and fiscal period id to the procedure dynamically.

Please suggest.

declare @xmla varchar(max) = '
{
  "createOrReplace": {
    "object": {
      "database": "FlashArchive",
      "table": "FORECAST_FLASH",
      "partition": "FORECAST_FLASH_368"
    },
    "partition": {
      "name": "FORECAST_FLASH_368",
      "source": {
        "query": "select * from FORECAST_FLASH_ARCHIVE_V where fiscal_period_id=368",
        "dataSource": "itv.world"
      }
    }
  }
}
';

EXEC (@xmla) AT SSAS;



declare @xmlap varchar(max) = '
{
  "refresh": {
    "type": "automatic",
    "objects": [
      {
        "database": "FlashArchive",
        "table": "FORECAST_FLASH",
        "partition": "FORECAST_FLASH_368"
      }
    ]
  }
}
';
exec (@xmlap) at SSAS;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
saij
  • 57
  • 2
  • 7
  • So basically you want to create a stored proc that takes a fiscal period, which is a number, for example, 368, and creates a partition and refreshes that partition. It would appear that all you need to do is concatenate this number into the correct part of the string. Do you know how to concatenate strings in T-SQL? – Nick.Mc Mar 26 '19 at 11:30
  • @Nick.McDermaid I want to pass partition name and fiscal period dynamically to a stored procedure. – saij Mar 26 '19 at 11:39
  • If the fiscal period is `368` then the partition name is just `FORECAST_FLASH_368` right? so you only need to pass in the fiscal period. Your first task is to work out how to create a stored prcoedure that takes a `VARCHAR` parameter – Nick.Mc Mar 26 '19 at 11:49
  • @Nick.McDermaid I have created stored procedure,which takes VARCHAR parameter.I am unable to find that how to fix above code in procedure and execute in linked server. – saij Mar 26 '19 at 11:59
  • Can you edit your question and post the entire stored procedure. If your parameter name is `@FiscalPeriod` then all you have to do is concatenate that to your existing strings. If you are having issues please explain in detail. – Nick.Mc Mar 26 '19 at 13:01

0 Answers0