如何使用Mssql将表参数添加到存储过程。 [英] How Can I Add Table Parameter To Stored Procedure Using Mssql.

问题描述

我有四个表管理员，学生，家长，老师，

我写了一个程序来调用一个工作正常的AdminLogin代码。

I have four table " Admin,Student,Parent,Teacher ",
I wrote a procedure to call a AdminLogin that is working fine below is the code.

CREATE PROCEDURE Login(@useName varchar(50),@pass varchar(50))
AS BEGIN
SELECT COUNT(*) FROM Admin WHERE name = @useName AND password = @pass
END

实际上我想改变管理员分别作为学生，老师，家长（角色）。



所以我尝试更改程序调用如下

Actually i want to change the Admin as Student, Teacher ,Parent (roles) respectively.

So i try to change the procedure call as follows

<pre lang="sql">CREATE PROCEDURE Login(@useName varchar(50),@pass varchar(50),@user varchar(50))
AS BEGIN
SELECT COUNT(*) FROM @user WHERE name = @useName AND password = @pass
END

但是我不能添加这个程序，我怎么能达到这个目的。





我的基本需求是。



四位用户

1.Admin

2.老师

3.学生

4.Parent



我从这里得到这个组合框。



来自textBox的用户名

来自textBox的密码



这是我的c＃按钮点击事件代码

But i cant add this procedure , How can i attain this.


My basic need is.

Four users
1.Admin
2.Teacher
3.Student
4.Parent

I am getting this from the combobox.

UserName from textBox
Password from textBox

This is my c# button click event code

<pre lang="cs">private void loginButton_Click(object sender, EventArgs e)
{
    username = userNameTextBox.Text;
    password = passwordTextBox.Text;
    role = roleSelectionComboBox.Text;
    if(username == "" || password == "" || role =="")
    {
        MessageBox.Show("Please fill all");
    }
    else
    {
        dbcon.Connection();
        query = "Login";
        com = new SqlCommand(query,dbcon.con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@useName", username);
        com.Parameters.AddWithValue("@pass", password);
        com.Parameters.AddWithValue("@user", role);
        int userCount = (Int32)com.ExecuteScalar();
        if(userCount == 1)
        {
            this.Hide();
            Dashboard AdminDashboard = new Dashboard(username);
            AdminDashboard.Show();
        }
        else
        {
            MessageBox.Show("count is 1");
        }
    }
}

推荐答案

不，你不能这样做，通过一个作为参数的表名（实际上你可以，但你必须创建adhoc sql才能这样做）。



你需要的是有效的开关查询正确表的语句，但这一切都非常不愉快。大开关混淆了Sql Server使用的查询计划。



必须这样做也是没有正确构建数据的症状。几乎可以肯定，这四个表应该只有一个，在某些方面使用一列来辨别类型。然后你的proc变得非常简单。

Nope, you can't do that, pass a table name in as a parameter (well actually you could but then you'd have to create adhoc sql to do this).

What you would need is effectively a switch statement which queries against the correct table, but all this is very unpleasant. Big switches confound the query plan Sql Server uses.

Having to do that is also symptomatic of not having your data structured correctly. Almost certainly these four tables should be just one, with a column used in someway to discern between the types. Then you proc becomes very simple.

Rob Philop正在创建一个视图来解决这个问题，因为我打算采用这种方法。



我创建了一个视图

Rob Philop Is creating a view solve this problem or not because i am planning to go with that approach.

I have created a view

CREATE VIEW CommonTable AS 

SELECT 'Admin' AS userTable, * FROM Admin

UNION ALL

SELECT 'Student' AS userTable, * FROM Student

UNION ALL

SELECT 'Parent' AS userTable, * FROM Parent

UNION ALL

SELECT 'Teacher' AS userTable, * FROM Teacher

这篇关于如何使用Mssql将表参数添加到存储过程。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！