select an int using variable column name..

Last Post 12 Sep 2006 04:30 PM by mwesch. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mickgough
New Member
New Member

--
11 Sep 2006 02:32 PM
Hi, I'm trying to update an INT value in table by selecting it using a VARCHAR/concatenated column name...
Really I need to get the int value & add an another int to it...
I keep getting "error converting the varchar value to a column of data type int"

This is an example, just with the values hardcoded for testing..
[from reading other recent posts I'd like to avoid sql injection also, If there is a way to get the int value back as an int variable to the main procedure that would be perfect]
any ideas?
Thx M

---
DECLARE @TEST TABLE
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO @TEST VALUES ('a',100,125,50)
INSERT INTO @TEST VALUES ('b',200,0,500)
INSERT INTO @TEST VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
--DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
--SET @AdditionalAllocation = 125
SET @Product = 'a'

DECLARE @CurrentValue INT
SET @CurrentValue =
('SELECT ' + @TargetShift + ' FROM tbltest where Product = ' + '''' + @Product)
--select * from @TEST
---
mwesch
New Member
New Member

--
11 Sep 2006 05:00 PM
If your data is in a real table (what you refer to a tbltest) and not in a local table variable (what you have as @test) then you can do the following.

drop table tbltest

create table tbltest
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO tbltest VALUES ('a',100,125,50)
INSERT INTO tbltest VALUES ('b',200,0,500)
INSERT INTO tbltest VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
--DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
--SET @AdditionalAllocation = 125
SET @Product = 'a'

declare @cmd varchar(5000)
select @cmd = 'SELECT ' + @TargetShift + ' FROM tbltest where Product = ''' + @Product + ''''

create table #results (value1 int)

insert #results
exec(@cmd)

declare @currentvalue int
select @currentvalue = value1 from #results
select @currentvalue

drop table #results
mickgough
New Member
New Member

--
11 Sep 2006 10:59 PM
Thx Michael
Apologies..the data is actually in a table variable...it gives error 'Must declare the variable '@Test' in the real table example..
---
DECLARE @TEST TABLE
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO @TEST VALUES ('a',100,125,50)
INSERT INTO @TEST VALUES ('b',200,0,500)
INSERT INTO @TEST VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
--DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
--SET @AdditionalAllocation = 125
SET @Product = 'a'

DECLARE @CurrentValue INT
SET @CurrentValue =
('SELECT ' + @TargetShift + ' FROM @TEST where Product = ' + '''' + @Product)
--select * from @TEST
mwesch
New Member
New Member

--
12 Sep 2006 03:04 AM
I can imagine a few situations where this might not be true, but if we're dealing with a locally defined table variable, then is there a need to use dynamic sql at all? Will someting like this work?

-----------------------------------------------------------------------------

DECLARE @TEST TABLE
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO @TEST VALUES ('a',100,125,50)
INSERT INTO @TEST VALUES ('b',200,0,500)
INSERT INTO @TEST VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
--DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
--SET @AdditionalAllocation = 125
SET @Product = 'a'

DECLARE @CurrentValue INT
select @CurrentValue = case @TargetShift when 'shift1' then Shift1 when 'shift2' then Shift2 else Shift3 end
from @Test
where Product = @Product

select @CurrentValue
mickgough
New Member
New Member

--
12 Sep 2006 04:38 AM
I actually got the permanent table solution you provided working in the stored procedure !! Thx
The table variable code is really good, I'm trying to use that example to update back to the table..only its only doing a variable assignment rather than the column update.. ahhhhh

--
SET NOCOUNT ON
DECLARE @TEST TABLE
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO @TEST VALUES ('a',100,125,50)
INSERT INTO @TEST VALUES ('b',200,0,500)
INSERT INTO @TEST VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
SET @AdditionalAllocation = 125
SET @Product = 'a'

DECLARE @CurrentValue INT
select @CurrentValue = case @TargetShift when 'shift1' then Shift1 when 'shift2' then Shift2 else Shift3 end
from @Test
where Product = @Product

--select @CurrentValue
UPDATE @TEST SET @TargetShift = @CurrentValue + @AdditionalAllocation
Print '@TargetShift equals ' + cast(@TargetShift as char)
select * from @TEST
--
mwesch
New Member
New Member

--
12 Sep 2006 04:30 PM
T-SQL does not allow you to use variables as table names or field names. The only option is to use dynamic SQL as you attempted in your first post (albeit not quite right).
mickgough
New Member
New Member

--
12 Sep 2006 10:59 PM
Ok thx, got the dynamic sql working..Thanks again for the help!

--
SET NOCOUNT ON
CREATE TABLE #TEST
(Product VARCHAR(1),
shift1 INT DEFAULT 0,
shift2 INT DEFAULT 0,
shift3 INT DEFAULT 0)

INSERT INTO #TEST VALUES ('a',125,125,50)
INSERT INTO #TEST VALUES ('b',200,0,500)
INSERT INTO #TEST VALUES ('c',0,125,0)

DECLARE @TargetShift VARCHAR(6)
DECLARE @AdditionalAllocation INT
DECLARE @Product VARCHAR(1)
SET @TargetShift = 'shift1'
SET @AdditionalAllocation = 125
SET @Product = 'a'

DECLARE @CurrentValue INT
select @CurrentValue = case @TargetShift when 'shift1' then Shift1 when 'shift2' then Shift2 else Shift3 end
from #TEST
where Product = @Product

DECLARE @TargetColumn VARCHAR(6)
SET @TargetColumn = @TargetShift
SET @TargetShift = @CurrentValue + @AdditionalAllocation
SELECT cast(@TargetShift as INT)

Print '@TargetShift equals ' + cast(@TargetShift as char)
Print '@TargetColumn equals ' + cast(@TargetColumn as char)

declare @MySql varchar(2000)
set @MySql = 'update #TEST set ' + @TargetColumn + ' = ' + @TargetShift + ' where Product = ''' + @Product + ''''
print (@MySql)
exec(@MySql)

select * from #TEST
DROP TABLE #TEST
--
You are not authorized to post a reply.

Acceptable Use Policy