Dynamically selecting column (SQL stored procedure)

Question

0.00/5 (No votes)

See more:

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?

SQL

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

Posted 7-Sep-11 2:06am

redoix

Updated 7-Sep-11 2:35am

Wendelius

v3

Add a Solution

3 solutions

Solution 1

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:

SQL

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

Posted 7-Sep-11 2:22am

Wendelius

Solution 3

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

Posted 12-Sep-11 16:11pm

redoix

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

md_refay · Accepted Answer · 2011-09-07T02:45:00

Solution 2

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

Posted 7-Sep-11 2:45am

md_refay

Dynamically selecting column (SQL stored procedure)

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Dynamically selecting column (SQL stored procedure)

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us