November 23, 2007 at 1:25 am
Hello
I am using Visual Studio 2005 (C#) with sql server 2005 Express Edition and I want to create a stored procedure that accepts a table as parameter
an parse that table after that
How can I create a stored procedure like that and how to send the table throw c#?
Thanks,
Daniela
Daniela
November 23, 2007 at 2:07 am
Daniela (11/23/2007)
HelloI am using Visual Studio 2005 (C#) with sql server 2005 Express Edition and I want to create a stored procedure that accepts a table as parameter
an parse that table after that
How can I create a stored procedure like that and how to send the table throw c#?
Thanks,
Daniela
Hi Daniela,
SQL Server does not support passing table variables as parameters to stored procedures, so you could only pass in the name of a table. Once you have the name and schema of a table, you could dynamically explore the underlying table using SQL queries targeting the sys.objects, sys.columns, etc. system views. If the table is a temporary table, you will need to explore the system views in tempdb.
Regards,
Andras
November 27, 2007 at 3:56 pm
November 28, 2007 at 12:58 am
Hi
I have a lot of inserts to do in database and i want to pass it all at once in sql server.
So, I decided to pass an sql large string and use EXECUTE to do it
Thank you all
Daniela
Daniela
November 28, 2007 at 1:52 am
November 28, 2007 at 7:24 am
I understand your problem as I have also needed the same set of functionality. The solutions presented below have been done within our app (.Net C# and SQL Server 2005).
You cannot pass an array (i.e., a table) as a parameter to a procedure or function in SQL Server 2005. This major limitation is being addressed in SQL Server 2008.
Therefore, you have two solutions:
1. Pass a delimited string of values and then pivot the values by a UDF that returns a table and perform an INSERT INTO ... SELECT ... FROM {UDF function} This has a number of limitations:
a.) it is only good for single values (i.e., not a record)
b.) it is only good for certain data types such as numbers, dates, and GUIDs. It would be quite unwieldy with string values (unless they are short, discrete values such as the U.S. state abbreviations or ISO country codes).
2. The best solution for passing in a table array into a SQL Server 2005 procedure is to pass the data as an XML document. You can use either Elements or Attributes to define the fields. Then the XML document can be shredded and all fields can be referenced. There are numerous articles on the topic. One of the best is by Jacob Sebastian.
Caveats:
a.) watch out for NULL values (xsi:nil="true"). It doesn't work correctly. Use a "special" value combined with NULLIF in the procedure.
Sample XML that we pass as a parameter value containing two (2) records with 5 fields (columns):
(I can't paste XML here so I'm attaching a TXT file that has the XML in it)
November 28, 2007 at 7:38 am
Opinion:
Passing in a large, constructed string performing an INSERT with of all of the values and then doing an EXECUTE is not an elegant way. First off, it exposes the risk to SQL injection along with the need to properly escape single quotes, etc. In my opinion, quite kludgy.
The C# layer (the BLL - business logic layer) shouldn't have to deal with the SQL nuances. That is why you should be using a stored procedure to do the database work.
November 28, 2007 at 12:34 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply