.NET Pass DataTable to Stored Procedure
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?
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
)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
ENDSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
So, passing in @prosmiedDate is the scalar variable. I have to use a cursor to get the data and assign to variables.
Right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Can I have the Begin Transaction, Commit Transaction, Rollback Transaction outside of the cursor to reverse any changes?
I am presuming yes.
ASKER
There will only be one row of data passed. That being the case how do I use it as scalar?