2

I have a stored procedure that I want to use to create one row in a parent table [Test] and multiple rows in a child table [TestQuestion]. The parent and child table both have primary keys that are identity datatype. Here is what the child table looks like with some not relevant columns removed:

CREATE TABLE [dbo].[TestQuestion] (
    [TestQuestionId] INT            IDENTITY (1, 1) NOT NULL,
    [TestId]         INT            NOT NULL,
    [QuestionNumber] INT            NOT NULL  
);

Inserting into the parent table is easy as all parameters are supplied to the SP and I just map these to an insert and perform the insert. But the child data table ids are given as a parameter @qidsJSON containing ids in a JSON form like this:

parameterList.Add(new SqlParameter ("@qidsJSON", qids.ToJSONString()));

["3CEFF956-BF61-419E-8FB2-9D6A1B75B909","63E75A2D-9F45-43CC-B706-D9890A22577D"]

Is there a way that I can use TransactSQL to take the data from my @qidsJSON and have it insert a row into the TestQuestion table for every GUID that appears in the parameter?

Alternatively is there another way I could pass data in a parameter that contains mulitple GUIDs? I am using C# to formulate the input data from a C# List so I could create the data for the input parameter in any form that would be most easy for the stored procedure to use.

Jesuraja
  • 3,774
  • 4
  • 24
  • 48

1 Answers1

6

You can use Table variable parameter for your stored procedure :

CREATE TYPE GuidList AS TABLE (Id UNIQUEIDENTIFIER)

CREATE PROCEDURE test 
    @Ids dbo.GuidList READONLY
AS

Use following reference in order to use table variable parameter in C#:

How to pass table value parameters to stored procedure from .net code

C# and Table Value Parameters

Community
  • 1
  • 1
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
  • Your suggestion is very interesting and came just as I was adding a few more details to the question. I am using C# to create the parameter for a C# list. Is it possible for me to convert that list into a Table variable parameter? –  Jul 01 '14 at 09:07
  • You must create class GuidList in C# and add Id property on it. then create new parameter from your class and assign your values to it. and then execute your stored procedure with this parameter. – mehdi lotfi Jul 01 '14 at 09:09
  • Would it be possible for you to add a bit more details about this to the question to show how this could be done. I am very interested in doing it the way you have suggested but I am really not sure of how to implement it. By the way sorry about making those small changes to the question where I added the words C#. Your answer was so quick and these were added after your first reply. Thanks. –  Jul 01 '14 at 09:13
  • @medhi - I think it might be good if you could include an example into your answer to help others who might look at the question. I thought there were some rules about pointing to links inside of answers. Just a few lines would be a good help to the SO community. Thanks –  Jul 01 '14 at 09:40