CODEWITHSUNDEEP
sql-server
user1464139
user1464139

Reputation:

How can I iterate through rows of a user defined table datatype and insert into a table?

I created a user defined datatype in my database:

CREATE TYPE [dbo].[QuestionList] AS TABLE (
    [QuestionUId] UNIQUEIDENTIFIER NULL);

I then pass this into my stored procedure like this:

CREATE PROCEDURE dbo.sp_ins
       @Title          NVARCHAR (100) ,
       @Questions      QuestionList READONLY,
       @TopicId        INT                           
AS 
BEGIN

Can someone tell me how I can take the contents of the Questions parameter, iterate through it and insert the contents along with an incrementing QuestionNumber into this table:

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

Upvotes: 0

Views: 1190

Answers (1)

Andrei Shakh
Andrei Shakh

Reputation: 181

For me it looks that you don't need to iterate at all, simple set-based insert will do:

INSERT INTO dbo.TestQuestion (
    QuestionNumber,
    QuestionUId)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    QuestionUId
FROM @Questions

Upvotes: 1

Related Questions