未插入多个值
使用 SQL Server 2000
我想根据部门选择 id
ID Department
001 H001
002 F001
003 S001
004 D001
存储过程
ALTER PROC [dbo].[proc_Atnd1]
@Dep VARCHAR(100)
AS
BEGIN
DECLARE @SQL VARCHAR(1000)
DECLARE @Flag INT
SELECT @Flag=0
SELECT @SQL = 'SELECT ID FROM Table1 WHERE '
IF @Dep <>'All'
BEGIN
IF @Flag=1
BEGIN
SELECT @SQL = @SQL+' AND (DEPARTMENT IN ('''+@Dep+'''))'
END
ELSE
BEGIN
SELECT @SQL = @SQL+' (DEPARTMENT IN ('''+@Dep+'''))'
SELECT @Flag=1
END
END
if @SQL = 'SELECT ID FROM Table1 WHERE ' SELECT @SQL = 'SELECT ID FROM Table1'
Delete from tb_Sample
INSERT INTO tb_Sample EXEC(@SQL)
end
此存储过程适用于单个或所有部门，但不适用于多个部门。
- 如果我将值传递给存储过程
All意味着，它会显示该部门的所有 id - 如果我选择
H001意味着，它会显示H001 的 id部门。 - 如果我选择
H001和D001则意味着不显示任何内容。
我将多个部门的值（例如 H001,D001,F001）传递给存储过程。但存储过程没有插入所选部门的 ID
我的存储过程有什么问题？
需要存储过程帮助
Using SQL Server 2000
I want to select the id according to the department
ID Department
001 H001
002 F001
003 S001
004 D001
Stored procedure
ALTER PROC [dbo].[proc_Atnd1]
@Dep VARCHAR(100)
AS
BEGIN
DECLARE @SQL VARCHAR(1000)
DECLARE @Flag INT
SELECT @Flag=0
SELECT @SQL = 'SELECT ID FROM Table1 WHERE '
IF @Dep <>'All'
BEGIN
IF @Flag=1
BEGIN
SELECT @SQL = @SQL+' AND (DEPARTMENT IN ('''+@Dep+'''))'
END
ELSE
BEGIN
SELECT @SQL = @SQL+' (DEPARTMENT IN ('''+@Dep+'''))'
SELECT @Flag=1
END
END
if @SQL = 'SELECT ID FROM Table1 WHERE ' SELECT @SQL = 'SELECT ID FROM Table1'
Delete from tb_Sample
INSERT INTO tb_Sample EXEC(@SQL)
end
This stored procedure is working for individual or all departments, but it is not working for multiple departments.
- If I pass the value to stored procedure
Allmeans, it is displaying all the id for that department - If I select
H001means, it is displaying id for theH001department. - If I select
H001andD001means, nothing displaying.
I am passing a value for multiple departments like this H001,D001,F001 to the stored procedure. But stored procedure is not inserting an id for the selected department
What is the problem with my stored procedure?
Need stored procedure help
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，我不建议构建这样的动态 SQL 语句...它会让您容易受到 SQL 注入攻击。如果您必须传递带有逗号分隔列表的字符串，我会解析该列表并将每个单独的值插入到临时表中。从那里您可以连接到临时表，而不是使用
IN语句。但是，这里的问题是您用引号将逗号分隔的列表引起来，因此不要查找值
H001、D001和F001< /code>... 它正在寻找等于'H001', 'D001', 'F001'的单个值要解决您的问题，您可以将 select 语句更改为
：传递以逗号分隔的列表，将每个单独的值括在字符串内的引号中，如下所示：
编辑：从变量赋值更改为存储过程调用，以使其更清楚我的建议。
解决这些类型的问题时，一个有用的方法是
PRINT打印出您创建的 SQL，然后检查它并确保它按照您的预期进行操作。但我再次建议您不要采用这种方法，而是考虑解析列表并避免动态 SQL。
First, I wouldn't recommend building up dynamic SQL statements like this... it can make you vulnerable to SQL injection attacks. If you have to pass in a string with a comma-separated list, I would instead parse through that list and insert each individual value into a temporary table. From there you can join to the temporary table instead of using an
INstatement.However, the problem here is that you're surrounding the comma-separated list with quotes, so instead of looking for the values
H001,D001, andF001... it's looking for a single value equal to'H001', 'D001', 'F001'To fix your issue, you would change your select statement to:
And when you pass in the comma-separated list, wrap each individual value in quotes within the string like this:
EDIT: Changed from variable assignment to stored procedure call to make it more clear what I'm suggesting.
Something that is helpful when troubleshooting these types of issues is to
PRINTout the SQL that you've created, and then examine it and make sure it is doing what you expect.But again, I would suggest that you not take this approach and instead look into parsing the list and avoid dynamic SQL.
使用转义单引号而不是双引号将您的值传递给多个部门。例如：
Pass your value for multiple departments with escaped single-quotes, not double quotes. For example: