如何为2个表之间的一对多关系创建storedprocedure [英] how to create storedprocedure for one-to-many relationship between 2 tables

问题描述

我创建了2个表（一对多关系）。

table1：

id，studentname varchar，nooflanguagesknown int，langid int

table2：

langid int，languages varchar。



i使用gridview（for-loop）从前端传递值关于用户选择，

现在我需要创建存储过程。

1用户可以选择5种语言，其他可能是3.

我应该在存储过程中声明多少变量以插入2个表。

解决方案

正如我之前所写的那样（如何在gridview中从gridview动态添加列 [ ^ ]），您需要3张桌子。



你的SP看起来像：

 创建  PROCEDURE  GetUserLanguages（）
  @ UserID   INT  
  AS  
  BEGIN  
 
  SELECT  U.UserID，U.UserNa me，L.LandID，L.LangName 
  FROM  UserLangs  AS  UL  INNER   JOIN  MyUsers  AS  U  ON  Ul.UserID = U.UserID 
  INNER   JOIN 语言< span class =code-keyword> AS  L  AND  UL.LangID = L.LangID 
  WHERE  UL.UserID =  @ UserID  
 
  END  

i have created 2 tables (one-to-many relationship).
table1:
id,studentname varchar,nooflanguagesknown int,langid int
table2:
langid int,languages varchar.

i passed the values from front-end using gridview(for-loop) based on user selection,
Now i need to create stored procedure.
1 user may select 5 languages & other may be 3.
how many variables should i declare in stored procedure to insert into 2 tables.

解决方案

As i wrote before (how to add columns dynamically in sql from gridview[^]), you need 3 tables.

Your SP would looks like:

CREATE PROCEDURE GetUserLanguages()
    @UserID INT
AS
BEGIN

    SELECT U.UserID, U.UserName, L.LandID, L.LangName
    FROM UserLangs AS UL INNER JOIN MyUsers AS U ON Ul.UserID = U.UserID 
                         INNER JOIN Languages AS L AND UL.LangID = L.LangID
    WHERE UL.UserID = @UserID

END

这篇关于如何为2个表之间的一对多关系创建storedprocedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！