在SQL查询中以caluse参数化 [英] Parameterized in caluse in SQL query

问题描述

Hello Experts，



我有一个学生表。

Hello Experts,

I have a table student.

select * from Table1 


ID	Name	Course	GPA
1	John	Maths	3
1	John	Science	3
1	John	French	3
2	bob	French	2
2	bob	Maths	3
3	Mike	Science	3
3	Mike	Law	4
4	Chelsa	Law	3
4	Chelsa	Maths	2
4	Chelsa	Science	1
5	Cloe	English	2
5	Cloe	French	3
5	Cloe	Chineese	3

现在我想写一个查询，根据他们的课程获取所有学生信息。

例如

Now I want to write a query that get all the student info based on course they took.
For example

select * from Table1 where Course in ('Science','Maths')

那个是内联查询...现在我想使用参数化查询。

我只以两门课程为例。但是如果我有多个怎么办？



我尝试过：



这是我尝试过的...

我在vb.net中将所有已检查的主题都放入一个字符串中

That is an inline query ... Now I want to use parameterized query for the same.
I only took two courses as example. But what if I have multiple?

What I have tried:

Here is what I have tried...
I have got all the checked subjects into a string in vb.net

Dim strCoursecode As New List(Of String)

For Each dr As DataRow In dtCoursecodes.Rows
      strCoursecode.Add(dr("Course"))
If strCoursecode.Count > 0 Then strCoursecode.Add(String.Empty)

String.Join(vbTab, strCoursecode.ToArray)

我的存储过程将传递'Maths Science等参数



@TEST是我的参数

My Stored procedure will pass parameters like 'Maths Science'

@TEST is my parameter

SELECT * FROM  TABLE1 WHERE COURSE IN ( @TEST )

现在任何人都可以帮我查询如何为IN子句传递参数化查询

Now can anyone help me with the query on how to pass parameterized query for IN clause

推荐答案

您有几个选择：



1.将CSV列表传递给存储过程。 在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]



2.查看用户定义的表类型。 sql server 2008 - 如何将用户定义的表类型作为存储过程参数传递给C＃ - Stack Overflow [ ^ ]

You have a couple of options:

1. Pass a CSV list to the stored proc. Using comma separated value parameter strings in SQL IN clauses[^]

2. Look at user defined table types. sql server 2008 - How to pass User Defined Table Type as Stored Procedured parameter in C# - Stack Overflow[^]

这篇关于在SQL查询中以caluse参数化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！