使用表数据类型更新表 [英] update table using table datatype

问题描述

你好，

我在将数据表参数传递给存储过程时遇到了一些问题.

有两个表
tblQuestion和tblAnswerChoice

1. tblQuestion 用于存储问题，其列为
a)Qid int主键身份
b)问题ntext
c)创建日期日期时间

2. tblAnswerChoice 用于存储特定问题的答案选项，其列为
a)Ansid int主键身份
b)qid int
c)AnswerChoice ntext


现在关于问题，我想更新 tblAnswerChoice 的数据，我正在将datatable作为参数传递给datatabse.我需要用于更新数据库的存储过程.是否可以使用表数据类型.如果是，则如何编写存储过程以更新 tblAnswerChoice .如果在这种情况下不使用表存储过程，如何更新 tblAnswerChoice

谢谢，
Deepak Pandey

Hello,

I am facing some problem in passing data table parameter to stored procedure.

There are two table
tblQuestion and tblAnswerChoice

1. tblQuestion is for storing questions and its columns are
a)Qid int primary key identity
b)Question ntext
c)Createiondate datetime

2. tblAnswerChoice is for storing Answer Choices for the particular question and its columns are
a)Ansid int primary key identity
b)qid int
c)AnswerChoice ntext


Now about the problem I want to update the data of the tblAnswerChoiceI am passing the datatable as parameter to the datatabse. I need the stored procedure for updating the datatbase. Is is possible using table datatype or not. If yes then how to write the stored procedure for update the tblAnswerChoice. If table stored procedure is not use in this case, how can I update the tblAnswerChoice

Thanks,
Deepak Pandey

推荐答案

您键入的应为

You type should be

CREATE TYPE [dbo].[tblAnswerChoiceTypeUpdate] AS TABLE(
    [AnsID]         int,
    [answerchoice] [ntext] NULL
)

存储过程应为

The stored procedure should be

CREATE procedure [dbo].[spUpdateAnswerChoice] @dtanswerchoice tblAnswerChoiceTypeUpdate READONLY as
Begin

    update tblAnswerChoice
    set AnswerChoice=@dtanswerchoice.answerchoice
    from tblAnswerChoice,@dtanswerchoice
    where tblAnswerChoice.AnsID=@dtanswerchoice.AnsID

End

希望对您有帮助

Hope this helps

这篇关于使用表数据类型更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！