.NET Pass DataTable to Stored Procedure

CipherIS
CipherIS used Ask the Experts™
on
I want to pass a datatable to a stored procedures.  I found the below article and have been using this as my template.

https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx

I created the below Type successfully but when trying to use it in the stored procedure I'm getting below error.

Msg 137, Level 16, State 1, Procedure POProcess, Line 31 [Batch Start Line 7]
Must declare the scalar variable "@POC".

How do I fix this?

CREATE TYPE POComponent AS TABLE (
	[id_PO] [int] IDENTITY(1,1) NOT NULL,
	[promisedDate] [datetime] NULL,
	[qty] [int] NULL
)

Open in new window

ALTER PROCEDURE [dbo].[POProcess] (
	@POC POComponentType READONLY
) AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		UPDATE POComponent
		SET promisedDate =	@POC.promisedDate,
		qty =				@POC.qty
		WHERE id_PO =		@POC.id_PO

END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Table type means vector variable where many records could be passed to SP. But you use table parameter in SP as scalar parameter - where only record could be presented.

So, if you really need to pass a table of records, you should work with this table parameter as with a table, like the following:

ALTER PROCEDURE [dbo].[POProcess] (
	@POC POComponentType READONLY
) AS

BEGIN
DECLARE @promisedDate DATE;
DECLARE @qty integer;
DECLARE @id_PO integer;

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE POComponentCursor CURSOR FAST_FORWARD
	FOR SELECT promisedDate, qty, id_POC FROM @POC;
	
	OPEN POComponentCursor ;
	FETCH NEXT FROM POComponentCursor 
	INTO @promisedDate, @qty, @id_POC 

	WHILE @@FETCH_STATUS=0
	BEGIN
		UPDATE POComponent
		SET promisedDate =	@promisedDate,
		qty =			@qty
		WHERE id_PO =		@id_PO

		FETCH NEXT FROM POComponentCursor 
		INTO @promisedDate, @qty, @id_POC 
	END;
	
	CLOSE POComponentCursor;
	DEALLOCATE POComponentCursor ;
END

Open in new window


It you in fact pass only record there you don't need table type, you could pass just three varables (@promisedDate, @qty, @id_POC ) in a regular way

Author

Commented:
Thanks.  I know that it looks like only 3 fields because that is all I provided in my code, but there are two tables and each have approximately 10 fields each.

There will only be one row of data passed.  That being the case how do I use it as scalar?
If you pass sinlge record list these 10 parameter is your SP parameters list like the following:

ALTER PROCEDURE [dbo].[POProcess] (
@promisedDate DATE,
@qty integer,
@id_PO integer
) AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		UPDATE POComponent
		SET promisedDate =	@promisedDate,
		qty =				@qty
		WHERE id_PO =		@Pid_PO

END

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Then there will be 20 parameters that I'm passing which is why I would rather create a table.  There are two datatables I need to pass.

So, passing in @prosmiedDate is the scalar variable.  I have to use a cursor to get the data and assign to variables.

Right?
Right, either you make cursor outside of SP, fetch values into scalar variable and pass them into SP, or you pass a table into SP and fetch cursor in SP.
Both options work

Author

Commented:
Do you know why the example says:

In the body of the stored procedure, you can use the parameter like any other table. This example joins the parameter to another table in the actual database to find some matching rows and return the result:

Create PROCEDURE dbo.GetJobs
  @JobCriteria JobSpecifications ReadOnly
As

  Select *
    From JobOpenings jo
      Inner Join @JobCriteria jc
        On  jo.JobName = jc.Name
        And jo.StartDate > jc.AvailableDate
Return

I'm finding this not to be true as I would have to use a cursor and put it in another table or variables.
You could do it this way:

ALTER PROCEDURE [dbo].[POProcess] (
	@POC POComponentType READONLY
) AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE
    POComponent
SET
A.promisedDate =	B.promisedDate,
A.qty =			B.qty
FROM
    POComponent AS A
    INNER JOIN @POC AS B
    ON A.id_PO = B.id_PO

END

Open in new window

Author

Commented:
One more question.  Using the cursor, I will be using two of them.  I will be checking the @@rowcount to ensure that it was successful.

Can I have the Begin Transaction, Commit Transaction, Rollback Transaction outside of the cursor to reverse any changes?  

I am presuming yes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial