title: What will be STRING_SPLIT function alternative in DataSet SSRS
body: <blockquote>
    <p>DataSet:dsStudent (It's a DropDown in the Report)</p>
  </blockquote>
  
  <pre><code>DECLARE @gid VARCHAR(36) = CONVERT(VARCHAR(36), NEWID());
  
  SELECT CAST(StudentId AS VARCHAR(MAX))+@gid AS StudentId, StudentName
  FROM dbo.Student1;
  </code></pre>
  
  <blockquote>
    <p>DataSet:dsStudentFilter (Use to Capture data selected from DropDown)</p>
  </blockquote>
  
  <pre><code>SELECT StudentName
  FROM Student1
  WHERE StudentId IN
        (
            SELECT REPLACE(b.value('text()[1]', 'NVARCHAR(MAX)'), RIGHT(b.value('text()[1]', 'NVARCHAR(MAX)'), 36), '')
            FROM
            (
                VALUES
                    (CAST('&lt;x&gt;' + REPLACE(@StudentId, ',', '&lt;/x&gt;&lt;x&gt;') + '&lt;/x&gt;' AS XML))
            ) A (a)
                CROSS APPLY a.nodes('/x') B(b)
        );
  </code></pre>
  
  <p>Now, when I preview the report I am getting below error:</p>
  
  <p><a href="https://i.stack.imgur.com/xYZcH.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xYZcH.jpg" alt="enter image description here"></a></p>
  
  <p>Report Design:</p>
  
  <p><a href="https://i.stack.imgur.com/mfQNt.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mfQNt.jpg" alt="enter image description here"></a></p>
  
  <p>In dsStudentFilter DataSet of SSRS report I want to do something like below:</p>
  
  <pre><code>DECLARE @StudentId VARCHAR(MAX)
  = '1111112EE300718-79A4-4260-A5E9-22B7CA71998,1111122EE300718-79A4-4260-A5E9-22B7CA71998';
  SELECT StudentName
  FROM Student1
  WHERE StudentId IN
    (
        SELECT value
        FROM STRING_SPLIT(REPLACE(@StudentId, RIGHT(@StudentId, 36), ''), ',')
    );
  </code></pre>
  
  <p>But I cannot use STRING_SPLIT in DataSet is there any alternative to do so?</p>
  
  <p>Data in the table:</p>
  
  <p><a href="https://i.stack.imgur.com/k70We.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k70We.jpg" alt="enter image description here"></a></p>
  
  <p>Above query will display Jay and Sam.</p>