title: How to split output after executing SQL Server stored procedure into rows?
body: <p>I'm now writing a script in SQL so that I can execute the output in Powershell to generate report. The code goes like this.</p>
  
  <pre><code>declare @from datetime, @to datetime
  select @from=GETDATE(), @to=GETDATE()+1
  
  SELECT ---$shape500.text='+'"'+'Assignation de Barrières-' + DATENAME(WEEKDAY,@from)+','+DATENAME(MONTH,@from)+' '+DATENAME(DAY,@from)+','+DATENAME(YEAR,@from)+'"'
  +'$shape'+cast(ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+
  '=$page.DrawRectangle'+'('+CAST(X1 as varchar)+','+CAST(Y1 as varchar)+','+CAST(X2 as varchar)+','+CAST(Y2 as varchar)+')'
  +'$shape'+ cast(ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.'+'linestyle="Gantt"'
  +'$shape'+ cast(ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.'+'textstyle="Gantt"'+case when left(arrival, 2)='AC' THEN 'Red' ELSE '' END+ CASE WHEN LEFT(Departure, 2)='AC' THEN 'Red' ELSE '' END +' '+' '
  ---+'$shape'+ cast(ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.FillStyle="Gantt"'+CASE WHEN CHARINDEX()
  +'$shape'+ cast(ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.Text="'+case when LEN(arrival)&lt;&gt;0 THEN Arrival ELSE '###' END +'/'+ [ACType Iata]+'/'+ CASE WHEN LEN(Departure)&lt;&gt;0 THEN Departure ELSE '###' END +'"'
  +'$shape'+ cast(100+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'=$page.DrawLine('+cast(X1 -0.5 as varchar)+','+cast(Y1 -0.03 as varchar)+','+cast(X1 +0.5 as varchar)+','+cast(Y1 -0.03 as varchar)+ ')'
  +'$shape'+ cast(100+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.Text="'+CONVERT(char(5), [Allocation Start Datetime], 108)+'"'
  +'$shape'+ cast(100+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.TextStyle="Times"'
  +'$shape'+ cast(100+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.LineStyle="Times"'
  +'$shape'+ cast(200+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'=$page.DrawLine('+cast(X2 -0.1 as varchar)+','+cast(Y1 -0.03 as varchar)+','+cast(X1 +0.1 as varchar)+','+cast(Y1 -0.03 as varchar)+ ')'
  +'$shape'+ cast(200+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.Text="'+CONVERT(char(5), [Allocation End Datetime], 108)+'"'
  +'$shape'+ cast(200+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.TextStyle="Times"'
  +'$shape'+ cast(200+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'.LineStyle="Times"'
  +'$shape'+ cast(300+ROW_NUMBER()OVER(ORDER BY (SELECT 0)) as varchar)+'=$page.DrawLine('+cast(X1 as varchar)+','+cast(Y2 as varchar)+','
  
  FROM GanttReports(default,default)
  </code></pre>
  
  <p><code>GanttReport</code> is a stored procedure. The output will generate everything in one big string like </p>
  
  <blockquote>
    <p>$shape1=$page.DrawRectangle(42.666660,6.78,58.083330,7.025)$shape1.linestyle="Gantt"$shape1.textstyle="Gantt"  $shape1.Text="QK8718/DH1/QK8749"$shape101=$page.DrawLine(42.166660,6.75,43.166660,6.75)$shape101.Text="16:21"$shape101.TextStyle="Times"$shape101.LineStyle="Times"$shape201=$page.DrawLine(57.983330,6.75,42.766660,6.75)$shape201.Text="17:30"$shape201.TextStyle="Times"$shape201.LineStyle="Times"$shape301=$page.DrawLine(42.666660,7.025)</p>
  </blockquote>
  
  <p>However what I want is in separate rows. You can say split it using '$shape' as deliminator?</p>
  
  <p>I know I can create a split function as I searched in the forum, however, not sure how to use it in this case. I'm quite new in SQL Server, will appreciate if someone can help!</p>