Passing the value of the Table parameter to the stored procedure

Question

Here's an example on how to use table parameters in a SQL Server 2008 stored procedure using .NET.

And here is a list of parameter types in CF9 .

Q: Is it possible for ColdFusion to pass a table parameter to a Microsoft SQL Server 2008 stored procedure?

Answer 1

Not sure.

If you need to pass a table of information, it is probably best to use an XML data type.

Sample code here .

Answer 2

The short answer is: no support, he must vote for it!

Long answer: Coldfusion can use JDBC, which does not yet support TVP, but should. Vote for this feature here: http://mssqlconn.uservoice.com/forums/113295-feature-feedback/suggestions/2269687-table-valued-parameters-tvp-support-in-jdbc

XML will work, but using TVP client code and sproc are easier to read, write, view, and debug. In most cases, this happens faster, depending on the implementation of the API.

FYI using Oracle is no better. They have the ARRAY SQL data type (which is closest to TVP). It is also not supported by JDBC: Using array objects

Answer 3

I discovered this workaround. You can call the stored procedure from cfquery, so you can pass the TVP parameter with the table.

<cfquery datasource="" name=""> DECLARE @return_value int -- Create table value parameter DECLARE @DataTVP tDataTable; --Build Table INSERT INTO @DataTVP(DataId) VALUES (1),(2),(3) EXEC @return_value = P_DeleteItems @tvpData = @DataTVP --Pass table into Stored Procedure SELECT 'Return Value' = @return_value </cfquery> 

Answer 4

I think you need an XML data type for this. I will give an example here. ##

 -- Create a Database CREATE DATABASE DataTableTest USE DataTableTest GO --Create the sample tables CREATE TABLE Employees ( EmployeeID BIGINT IDENTITY(1,1), EmployeeName VARCHAR(50), DepartmentID BIGINT ) CREATE TABLE Departments ( DepartmentID BIGINT IDENTITY(1,1), DepartmentName VARCHAR(50) ) GO -- Populate the Sample Tables INSERT INTO Departments ( DepartmentName) SELECT 'IT' INSERT INTO Employees (EmployeeName, DepartmentID ) SELECT 'JCB', 1 GO 

Now create a stored procedure that returns two sets of results.

 CREATE PROCEDURE GetEmployeeInfo AS SET NOCOUNT ON SELECT EmployeeName, DepartmentID FROM Employees WHERE EmployeeID = 1 SELECT DepartmentName FROM Departments WHERE DepartmentID = 1 GO 

Create the following stored procedure that takes an XML parameter. This procedure will insert the data from the XML parameter into the Employee table.

 CREATE PROCEDURE ProcessXml ( @data XML ) AS INSERT INTO Employees(EmployeeName, DepartmentID) SELECT xdvalue('EmployeeName[1]','VARCHAR(50)') AS EmployeeName, xdvalue('DepartmentID[1]','INT') AS DepartmentID FROM @data.nodes('/NewDataSet/Table') x(d) GO