动态选择列(SQL存储过程) [英] Dynamically selecting column (SQL stored procedure)

问题描述

我正在创建一个程序，该程序将更新单行中动态选择的列；而且此需求很重要.

我是新手，会明确表示帮助

主持人:
如何将值@InpArgXEl分配给PatternDtab(数据库表)的变量@ColumnID指示的列，该列的主键列是PattnRowID?

I AM CREATING A PROCEDURE THAT WOULD UPDATE A DYNAMICALLY SELECTED COLUMN IN A SINGLE ROW; AND THERE IS STRONG REASON FOR THIS NEEED.

I AM A NEWBIE AND WOULD APPRICIATE EXPLICIT HELP

THE POSER:
How do I assign the value @InpArgXEl to the column, indicated by the variable @ColumnID, of PatternDtab (the database table) which primary key column is PattnRowID?

ALTER PROCEDURE dbo.TransPayPattn	
(
 @InpArgXEl XML OUTPUT,
 @ColumnID NCHAR(10),
 @RowNo INT OUTPUT
)

SET @ColumnID = '['+@Column+']'

--Litrarilly
UPDATE PatternDtab SET @ColumnID = @InpArgXEl WHERE PattnRowID=@RowNo

--what is the RIGHT way to do this?
DECLARE @Cmd  VARCHAR(MAX)

SET @Cmd= N'UPDATE PatternDtab SET '+@ColumnID+' = @InpArgXEl WHERE PattnRowID=@RowNo'

EXECUTE sp_executesql @Cmd

-- Confirmation
SET @InpArgXEl = (SELECT ... -- I ALSO NEED TO RE-EVALUATE THE CURRENT VALUE OF @ColumnID

推荐答案

这里是使用sp_executesql的一个小示例.过程中的至少一个问题是您根本没有定义参数:

Here''s a small example of using the sp_executesql. At least one problem in your procedure is that you don''t define the parameters at all:

CREATE TABLE DynTest (
  col1 varchar(100),
  col2 int
);

INSERT INTO DynTest VALUES ('A', 1);

CREATE PROCEDURE DynTestProc (@colName varchar(100), @value int) AS
DECLARE @sql        nvarchar(2000);
DECLARE @parameters nvarchar(2000);
BEGIN
  set @sql = N'UPDATE DynTest SET ' + @colName + ' = ''B'' WHERE col2 = @valueinsql';
  set @parameters = N'@valueinsql int';

  print @sql;
  print @parameters;

  exec sp_executesql @sql, @parameters, @valueinsql = @value;
END;

EXEC DynTestProc 'col1',1

SELECT * FROM DynTest

EXEC DynTestProc 'nonexistentcol',1 <-- error

好，我知道了.
感谢Mika Wendelius和md_refay！

OK, I''VE GOT IT.
THANKS TO U Mika Wendelius AND TO U TOO md_refay!

我"只会写upata语句

声明@sql nvarchar(max)
选择@sql =''update DynTest Set''+ @ ColName +''=''''''+ @ InpArgXEl +''''其中PattnRowID =''+ convert(nvarchar(5)，@ RowNo)

秘诀是您必须在''''
之间传递字符串值 希望对您有帮助

i ''ll write upata statment only

declare @sql nvarchar(max)
select @sql=''update DynTest Set ''+@ColName +'' = ''''''+@InpArgXEl+ '''''' where PattnRowID =''+convert(nvarchar(5),@RowNo)

the secret is u must pass string value between ''''
hope it be helpfull

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