1

I want to create a stored procedure which inserts some records from Archive table into the main table.

My problem is that the client has multiple checkboxes in his grid and he/she can check any number of checkboxes. Each checkbox represents a record in the Archive table. Each record is identified by orderNo. On the basis of orderNo I want to make a Stored Procedure that takes a record from the Archive table and inserts this into the main table.

My question: How do I handle an unknown number of parameters in this Stored Procedure effectively? In pseudo-code:

create procedure moveRecords(what parameters i should take here)
as 
begin
insert into mainTable(orderNo,date,siteID) select orderNo,date,siteID from 
ArchiveTable where orderNo=<here what i write>

The client could send 1 parameter, 2, 3, 10, 20 parameter etc we don't know. Something like the following:

exec moveRecords(any no. of parameters)

I came up with a solution like the following:

  1. By putting all orderNo (sent by the client) into one temp_Table and retrieve these from temp_Table in my Stored Procedure.
  2. At the server side we take all orderNo in an array and call procedure on the basis of this array.

I am looking for alternatives.

TT.
  • 15,774
  • 6
  • 47
  • 88
Arun nagar
  • 178
  • 4
  • 13

3 Answers3

5

If you use MSSQL 2008 or newer you can use TVP (Table Valued Parameters). Here is an article describing them. And here is a question about using TVP from C#.

TVP are pretty easy to use:

  1. Firstly you have to define a type of TVP by using CREATE TYPE dbo.NameOfYourType ... command.
  2. Then you have to modify your stored procedure. For example: create procedure moveRecords(@Input dbo.NameOfYourType READONLY) ....
  3. Then call this stored procedure from C# as any other stored procedure. TVP parameters are represented by DataTable in C#.
Community
  • 1
  • 1
Michał Komorowski
  • 6,198
  • 1
  • 20
  • 24
  • :-i could not understand your 3 step please can you define it more.How to insert values in our Table type – Arun nagar Jan 15 '16 at 10:11
  • Did you read a question pointed by me? There is an answer that refers to an article that point by point explains how to use TVP from C#. You can also read this one: https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx – Michał Komorowski Jan 15 '16 at 10:17
0

You can pass xml and parse it inside procedure as described here. This sa,mple also describe table valued parameters.

Community
  • 1
  • 1
Klaudiusz bryjamus
  • 326
  • 3
  • 12
0
create procedure Usp_Testproc
(
@iordernumber int
)
as 
begin
insert into mainTable(orderNo,date,siteID) 
select orderNo,date,siteID from ArchiveTable where orderNo=@iordernumber
Bharadwaj
  • 2,535
  • 1
  • 22
  • 35
Asif Ganaie
  • 101
  • 9