can we pass datatable to store procedure

Question

0.00/5 (No votes)

See more:

hi
i m little bit confuse that can we pass complete datatable or dataset to store procedure
and how to use it in store procedure.

amit jamwal

Posted 1-Nov-12 19:21pm

Amit Jamwal

Add a Solution

3 solutions

Solution 1

Hi,

See the below link. It might be help you.

http://beyondrelational.com/modules/2/blogs/28/posts/10441/passing-a-data-table-to-a-sql-server-2005-stored-procedure.aspx[^]

Copy Data from a DataTable to a SQLServer Database using SQLServer Management Objects and SqlBulkCopy[^]

Passing an array or DataTable into a stored procedure[^]

Posted 1-Nov-12 19:30pm

VIPR@T

Comments

Amit Jamwal 2-Nov-12 1:40am

thnx for ur reply...
but my prob is that i hav a table emp in which emp_code is there.Now i am uploading an exl file in which emp_code and amount is there. Wht i have to do is to first m filling a datatable from exl and after that i have to match those emp_code with my emp table.
and my exl file contains more than 7000 records and checking them one bye one in loop is time consuming so i m thinking to pass datatable to storproc and match there once...
any solution.....

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

damodara naidu betha · Accepted Answer · 2012-11-01T20:30:00

Hi,

these links may help you..

Sending a DataTable to a Stored Procedure[^]

Passing a datatable to a Stored Procedure in SQL Server 2008[^]

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx[^]

And use MERGE to update the EMP table inside the stored procedure...

Thank you

Gautam Raithatha · Accepted Answer · 2012-11-01T20:37:00

Solution 3

yes u can pass datatable to SP, but first u need to create a user-defined table type in SQL server, having same structure as datatable to be passed. For eg:

SQL

CREATE TYPE TypeName AS TABLE
(
	col1 int,
	col2 varchar(50)
)

then in SP parameter, use type "TypeName".

If u r using C#, while passing datatable to SP, use SqlDbType.Structured.

Posted 1-Nov-12 20:37pm

Gautam Raithatha

Comments

Amit Jamwal 2-Nov-12 2:45am

Thnx for reply..
on google i found same example.
but in my store procedure following error is coming

parameter cannot be declared as readonly as it is not table value type
plz give the soln for abov prob.....

Gautam Raithatha 2-Nov-12 2:53am

if u have created user defined type as Table, then it will not giv this type of error.

@ParamName TypeName READONLY

Here "TypeName" is table type so it should not give error:

CREATE TYPE TypeName AS TABLE
(
col1 int,
col2 varchar(50)
)