Using a variable column name...

Question

Using a variable column name...

bard-840479

SSC Veteran

Points: 287
More actions
August 5, 2008 at 2:22 pm

#125490

Hello SQL Gurus,
I have recently run across this and I don't know what to do. The site I have has a SQL 2000 database and I have been using stored procedures (about 200 in all) for all interactions between the DB and the client.
All of them work great with the exception of this one. It is the only one that uses variable column names.
Ok, here it is:
CREATE PROCEDURE [so_name]
@imgcount int,@Photo varchar(55),@Col Varchar(10),@ExpID int
AS
Declare @SQL varchar(500)
set @SQL=''
select @SQL='UPDATE Experience SET [' + @Col + ']=['+@Photo+']'
select @SQL=@SQL + ' WHERE ID='
select @SQL=@SQL + @ExpID
Execute(@SQL)
The result is an error.."Syntax error converting the varchar value "UPDATE Experience SET [photo1]=[photoname.jpg] WHERE ID=' to a column of data type int.
I do not see why it wants to convert it to an int. Photo1 is a column name in the table Experience. There are 5 photo columns in all.
I be stuck badly. This is such a hard topic to find anything on the web for, for some reason.
If anyone out there can help out, I sure will appreciate it!
Bard

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

bard (8/5/2008)
CREATE PROCEDURE [so_name]
@imgcount int,@Photo varchar(55),@Col Varchar(10),@ExpID int
AS
Declare @SQL varchar(500)
set @SQL=''
select @SQL='UPDATE Experience SET [' + @Col + ']=['+@Photo+']'
select @SQL=@SQL + ' WHERE ID='
select @SQL=@SQL + @ExpID
Execute(@SQL)

Your variable @SQL is declared as varchar and your @expID is declared as int. Then @SQL+@ExpID (varchar+int).

Did you try..

select @SQL=@SQL + cast(@ExpID as varchar(10))

"-=Still Learning=-"

Lester Policarpio

bard-840479 SSC Veteran Points: 287 More actions · Answer 2

Thanks, but that didn't work. I got an invalid column name error. it said that the photoname.jpg was an invalde name. hmmm...

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

Can you post some data?

refer to this link

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

"-=Still Learning=-"

Lester Policarpio

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

And, if you don't mind, tell us why it has to be a dynamic column name. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

bard-840479 SSC Veteran Points: 287 More actions · Answer 5

Okay, it is set up in an array on my site. It is the photo names. Instead of inserting each individual image name, it should be namable as photo1, photo2, etc. up photo5. Somehow i want to transfer the column name from the web site to SQL. I guess i could just do individual ones, and get around this whole thing. I donno, this is not my field of expertise. What do you all think?

bard-840479 SSC Veteran Points: 287 More actions · Answer 6

Okay, here is the table:

CREATE TABLE [dbo].[Experience] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Date] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Location] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Experience] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Photo1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Active] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UploadID] [int] NULL ,

[Photo2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Photo3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Photo4] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Photo5] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Thumb1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Thumb2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Thumb3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Thumb5] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Thumb4] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [PK_Experience] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

bard-840479 SSC Veteran Points: 287 More actions · Answer 7

Never mind. I just had it update individual columns. Dynamic columns not going to work for me. Thanks anyway people!:D