Passing non-scalar data for multiple columns using runstoredprocedure

Hello, I am trying to pass non-scalar data into an SQL Server stored procedure using the "runstoredprocedure" function in the Database Toolbox. The syntax for doing so was not very clear. How should we proceed? The data may be associated with multiple columns.

The stored procedure itself is written as follows:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE dbo.pc_NewRecord_tb_SerialNumberList
	-- Add the parameters for the stored procedure here
	@SerialNumberList aSerialNumberList READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO dbo.tb_SerialNumberList(serial_number)
	SELECT * FROM @SerialNumberList
END
GO

where aSerialNumberList is a user-defined table type, with a single column: serial_number (nvarchar(32)).

What's the correct call to "runstoredprocedure" to pass in a cell array of chars or a string array? Also, how would this syntax be extended to multiple columns (e.g. if aSerialNumberList had more than one column)?

Answer 1

[Sachin Lodhi]

Hi,

Based on my understanding, it seems that you are seeking guidance on how to pass non-scalar data to a stored procedure. The syntax for achieving this is as follows:

runstoredprocedure(c,'myproc',{2500,'Jones'}); 

In this example, 'c' represents the connection object, 'myproc' is the name of the procedure, and ‘2500’ and 'Jones' are the values to be inserted into the table.

To pass a cell array of chars or a string array, you can simply specify them as comma-separated values within the curly braces, similar to the code snippet provided above.

For additional insights and alternative approaches, please refer to following MATLAB Answers:

• https://www.mathworks.com/matlabcentral/answers/102214-how-do-i-call-a-stored-procedure-with-an-out-parameter-from-matlab-using-the-database-toolbox?s_tid=srchtitle
• https://www.mathworks.com/matlabcentral/answers/93035-how-do-i-execute-a-stored-procedure-with-the-database-toolbox?s_tid=srchtitle

I hope this helps.

Best Regards,

Sachin

[sst]

Hello Sachin, I believe that syntax still inserts one row of data. What I am looking to do is essentially insert a block of data. The alternative would be to call "runstoredprocedure" in a loop, but I was hoping to avoid that.

Regards.