using UPDATE in SQL?

Question

Post reply

using UPDATE in SQL?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
October 24, 2005 at 6:32 am

#188136

Can anyone help I'm trying to update a table on my SQL server, the line of code I'm using in the stored procedure is the following

CREATE PROCEDURE spSaveDaysArea @sName as VarChar(200),@sArea as VarChar(100), @sDate1 as VarChar(20)
AS

declare @sql as varchar(5000)
set @sql = 'update [TI Work] set [' + @sArea + ']' +' = [' + @sName + '] where [date1] = ' + @sDate1 + ''
exec (@sql)
GO

but I'm getting an invalid colomn error, any ideas?

Viewing 15 posts - 1 through 15 (of 32 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The Curse and Blessings of Dynamic SQL

Why are you using dynamic sql for this???

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

mick burden

SSCarpal Tunnel

Points: 4432

October 24, 2005 at 6:51 am

#599452

because I'm using a vairable for the field name

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ok, why using dynamic field names??

Did you read the link?

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

I did actually read the link before posting my question on the forum, I couldn't see anything that could help me, I'm just starting out using an SQL server and I'm in the process of converting some of my program in VB to use this

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

k, Go back one step. What problem are you trying to solve using this sp?

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 6

I'm trying to update a database using VB and passing the name of the field and the data I want to put in that particular field, if that makes sence, incidently the field @sDate1 is a date/time field

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Not the way you're doing it. You should use static sql for this.

UPDATE dboTable set Col1 = @Param1, Col2 = @Param2 where DateKey = @DateVariable

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 8

I've actually got it working. At Last !!!

here's what I used :-

CREATE PROCEDURE spSaveDaysArea @sName as VarChar(200),@sArea as VarChar(100), @sDate1 as VarChar(100)

AS

declare @sql as varchar(5000)

set @sql = 'update [TI Work] set ['+ @sArea +']' + ' = '''+ @sName +''' where [date1] = ''' + @sDate1 + ''' '

exec (@sql)

GO

Many thanks for the assistance

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Let me rephrase what I meant this whole time in clearer terms.

1 - You totally missed out the real point of store procs.

2 - You're going against every best practices in the book.

3 - Your server is now prone to sql injection attacks meaning that someone can take control of the compagny you are working for and YOU'RE RESPONSIBLE OF THAT.

Now go read the freaking link I gave you and ask question if you don't understand how to do that update.