How to Pass and Consume MultiValue String parameter in SQL Stored Proc

How to Pass and Consume MultiValue String parameter in SQL Stored Proc

Posted by: stan on 2 January 2018, 5:42 pm EST

• 

  stan

  • Post Options:
  • Link
    Copy

  Posted 2 January 2018, 5:42 pm EST

  We’re currently evaluating Active Reports on a 30-day trial. We’ve been successful in passing a multivalued integer parameter to a stored procedure by defining the parameter in the stored proc as NVARCHAR(4000) and then using String_Split to create a table of the integer values.

  However when we try to define a multi-valued string parameter to a proc where the parameter is defined as an NVARCHAR(4000) Active Reports tells us it is an invalid parameter.

  I’m unable to find anything in the documentation that deals with passing multi-valued parameters to stored procs.

  Can you tell me how this can be done?
  
  Thanks
  
  Stan

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 3 January 2018, 2:48 am EST

  Hello,

  In order to achieve you requirement, you need to use JOIN function while passing parameter in stored procedure. Please refer the attached report for implementing the same.
  
  Also, refer the below stored procedure which has been used in report

  USE [testDb]
  
  GO

  SET ANSI_NULLS ON
  
  GO

  SET QUOTED_IDENTIFIER ON
  
  GO

  CREATE PROCEDURE [dbo].[create_Table]
  
  @LastName nvarchar(4000)
  
  AS

  SELECT *
  
  FROM STRING_SPLIT(@LastName,‘,’)
  
  GO

  Thanks,
  
  Mohit

  rpt_MultiValue.zip