处理具有变量名的列(SQL存储过程) [英] Handling columns with variable names (SQL stored procedure)

问题描述

我需要在数据表列上使用变量句柄，但不知道如何操作.我还是这里的菜鸟

如何将值@PayPattnXEl分配给变量@ColumnID.
指示的列

I need to have a variable handle on a data table column but don''t know how. I am a rookie here anyway

How do I assign the value @PayPattnXEl to the column indicated by the variable @ColumnID.

ALTER PROCEDURE dbo.TransPayPattn	
(
 @InputArgs VARCHAR(MAX) OUTPUT,
 @ColumnID NCHAR(10),
 @RowNo INT OUTPUT,
 @Response   BIT OUTPUT
)

SET @Column = '['+@Column+']'
UPDATE PayPatternDtab SET @ColumnID = @PayPattnXEl WHERE PattnRowID=@RowNo

推荐答案

是否表示要动态更改列以在SQL语句(和过程内)中进行更新.

如果正确，则无法直接执行.在我看来，在寻求解决方案之前，除非您有充分的理由，否则这甚至不是您应该做的.

您对目标一无所知，但似乎您正在创建一个过程，一次可以更新一行中的一列.如果是真的，为什么呢?例如，如果在单行中更新了几列，则可能导致客户端和数据库之间的往返次数过多.同样，这种结构可能会导致其他一些问题和额外的编码.

无论如何，如果需要在过程内执行动态SQL语句，则基本上有两个选择:
-执行 [ sp_executesql [

Do you mean you want to dynamically change the column to update in your SQL statement (and inside a procedure).

If that''s correct, you cannot do it directly. Before jumping to solutions, in my opinion this isn''t what you should even do unless you have strong reasons.

Don''t know anything more about your goal but it looks like you''re creating a procedure that would update a single column in a single row at a time. If it''s true, why? For example, this may lead to excessive amount of round trips between the client and the database if several columns are updated in a single row. Also that kind of structure may lead to several other problems and extra coding.

Anyhow, if you need to execute dynamic SQL statements inside a procedure, you basically have two options:
- EXECUTE[^]
- sp_executesql[^]

这篇关于处理具有变量名的列(SQL存储过程)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！