June 20, 2003 at 5:44 am
New here, I'm looking around and have not found this yet, so I'm going to ask this as a question. I'm working on writing a update t-sql statement as a stored procedure, but the update fields need to unknown, that is, could be 5 fields this time, maybe 20 fields next time. Although ADO can do this, I'm trying to push this back to the database server, anyone have any thoughts?
June 20, 2003 at 5:50 am
Hi TaffyLewis,
quote:
that is, could be 5 fields this time, maybe 20 fields next time.
5 fields to be updated, or 5 to choose from which is updated?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 20, 2003 at 6:07 am
If I understand you here, you want to update a single row in a table, but you only want to change certain fields, and the fields that you want to change can be different each time?
Here is a (inefficient and horrible) way to achieve this. Basically you pass in the ID of the row to be updated (or whatever you need to get it) and the update stored procedure takes ALL of the fields that can be updated as optional parameters.
CREATE PROCEDURE testUpdate @intID as int,
@intCol1 as int=null,
@intCol2 as int=null
AS
IF @intCol1 is not null UPDATE tableb SET col1=@intCol1 WHERE ID=@intID
IF @intCol2 is not null UPDATE tableb SET col2=@intCol2 WHERE ID=@intID
... repeat for each updatable field ...
So:
testUpdate @intID=1, @col1=1 just changes col1
testUpdate @intID=1, @col2=1 just changes col2
testUpdate @intID=1, @col1=1, @col2=1 changes both col1 and col2.
June 20, 2003 at 6:44 am
With ADO, I guess that you are building the update statement on the client.
There is similar facility within SQL Server called dynamic sql. This is where you create an sql string within a stored procedure and then execute it.
WARNING (Frank is very hot on this). Using dynamic SQL does have a number of health warnings. In particular you need to consider SQL Injection attacks - there have been a number of posts on this site about injection attacks and dynamic SQL so go have a read.
The main drawback with dynamic SQL is that you have to give appropriate permissions for the user on the table ie insert, update, delete etc. With stored procedures, you can normally avoid this by giving users execute permission on the procedure only.
If you were planning to build the update statement on the client then you would have to grant appropriate permissions on the table so using dynamic sql is no different in that respect.
On a completly different tack, you might be able to avoid build an sql statement (either on the client or on the server). If the list of fields is finite, you could build a static stored procedure along the following lines:
create procedure update_table @col1 varchar(25) = null, @col2 varchar(25) = null, @col3 varchar(25) = null.... as
update table
set col1 = isNull(@col1,col1), col2 = isNull(@col2,col2), col3 = isNull(@col3,col3),,,,
where ...
When you call a procedure, you do not have to pass all the parameters:
exec update_table @col1 = 'New value for col1', @col3 = 'New value for col3'
When you call the procedure with col1 and col3 values, it updates the entire row but as @col2 is null, it is updated to the same value ie no change.
Food for thought
Jeremy
June 20, 2003 at 7:30 am
>>With ADO, I guess that you are building the update statement on the client.
In DAO you can do something like this to a recordset object.
rst.edit
rst.fields("Name")="Bungle"
rst.fields("SexualOrientation")="n/a"
...
...
rst.update
I'm pretty sure it's the same in ADO. Thinking about it, this probably does result in you producing an update statement but it's all nicely abstracted away in the update method.
>>update table
>>set col1 = isNull(@col1,col1), col2 = isNull(@col2,col2), col3 = isNull(@col3,col3),,,,
>>where ...
Oh yes, of course <ahem> somewhat neater than my suggestion - nice one.
June 23, 2003 at 9:51 am
Jeremy
I like your solution. I have had a similiar situation but I did not want to use dynamic sql to solve it. So, I ended up using a solution similiar to Planet115. It worked, but I thought there had to be a better solution. Thanks for the enlightenment.
June 23, 2003 at 12:55 pm
You can use a case statement and set the value back to itself if it doesn't fit certain criteria. An example below...
/*--------------------------------
Updates #Test1 with values from #Test2 only when the #Test2 values
are 10 times #Test1 values
*/--------------------------------
Create table #Test1 (intID int identity, col1 int, col2 int, col3 int)
Insert #Test1 values (100, 100, 100)
Insert #Test1 values (200, 300, 400)
Create table #Test2 (intID int identity, col1 int, col2 int, col3 int)
Insert #Test2 values (1000, 1000, 569)
Insert #Test2 values (2000, 5489, 4000)
Updatet1
Sett1.col1= Case
When t2.col1/10 = t1.col1 then t2.col1
Else t1.col1--This sets the values back to itself
End,
t1.col2= Case
When t2.col2/10 = t1.col2 then t2.col2
Else t1.col2
End,
t1.col3= Case
When t2.col3/10 = t1.col3 then t2.col3
Else t1.col3
End
From#Test1 t1 (nolock)
JOIN#Test2 t2 (nolock) on t1.intID = t2.IntID
--record 1 col3 not updated because 569/10 <> 100
--same for record 2 col2
select * from #Test1
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply