April 8, 2010 at 12:43 pm
Is there anyway to update a specific column based on a variable like the following....
declare @FieldValue varchar(50)
update table set @ColumnName = @FieldValue
without using dynamic sql or endless case statements?
April 8, 2010 at 12:47 pm
Why is it important to avoid dynamic SQL?
April 8, 2010 at 12:56 pm
I'm not totally against using dynamic sql.
But it can become more difficult to debug and maintain over time.
Also, there can be a performance loss.
April 8, 2010 at 1:06 pm
weezie (4/8/2010)
I'm not totally against using dynamic sql.But it can become more difficult to debug and maintain over time.
Also, there can be a performance loss.
It can be simpler to maintain than having 50 variations of a query coded in a stored procedure.
There is nothing inherent in dynamic SQL that results in performance loss compared to static SQL, and there are many situations where it will be better.
April 8, 2010 at 1:25 pm
Given the choice of:
if @Param1 > '' and @Param2 > '' update table1 set column1 = @param1, column2 = @param2
else if @Param1 > '' update table1 set column1 = @param1
else if @Param2 > '' update table1 set column2 = @param2
or
update table1
set column1 = case when @param1 > '' then @param1 else column1 end,
column2 = case when @param2 > '' then @param2 else column2 end
I know you said you didn't want case statements, but I'll take the second version.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply